Hi, I installed percona-server 5.5 on Ubuntu 12.04 using apt repository
It's working by default. But when I try to replace my.conf file with the configuration that generated from the online wizard , and restart the server, it fails with error like below:
df: `/var/lib/mysql/data//.': No such file or directory
df: no file systems processed
* /etc/init.d/mysql: ERROR: The partition with /var/lib/mysql/data/ is too full!
How can I solve this problem? Thanks!
The my.cnf file is like this:
[mysql]
# CLIENT #
port = 3306
socket = /var/lib/mysql/data/mysql.sock
[mysqld]
# GENERAL #
user = mysql
default_storage_engine = InnoDB
socket = /var/lib/mysql/data/mysql.sock
pid_file = /var/lib/mysql/data/mysql.pid
character-set-server=utf8
collation-server = utf8_unicode_ci
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 0.0.0.0
# MyISAM #
key_buffer_size = 32M
myisam_recover = FORCE,BACKUP
# SAFETY #
max_allowed_packet = 16M
max_connect_errors = 1000000
skip_name_resolve
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate_is_now = 1
innodb = FORCE
innodb_strict_mode = 1
# DATA STORAGE #
datadir = /var/lib/mysql/data/
# BINARY LOGGING #
log_bin = /var/lib/mysql/data/mysql-bin
expire_logs_days = 14
sync_binlog = 1
# CACHES AND LIMITS #
tmp_table_size = 32M
max_heap_table_size = 32M
query_cache_type = 0
query_cache_size = 0
max_connections = 500
thread_cache_size = 50
open_files_limit = 65535
table_definition_cache = 1024
table_open_cache = 2048
# INNODB #
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_buffer_pool_size = 512M
# LOGGING #
log_error = /var/lib/mysql/data/mysql-error.log
log_queries_not_using_indexes = 1
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/data/mysql-slow.log
Hi miguelangelnieto, thanks for your reply. The data folder does not exists.
Below is my default my.cnf after my first installation. Hope you can help me figure out what should I do.
By the way, why percona cnf file asks to move data to data folder instead of the default settings, is there any advantage for that or is it just a way to separate percona-server with mysql default server?
Thanks
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 16M
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
After I created the data folder under /var/lib/mysql looks like the errors before are gone. But it still failed to start.
I look at the mysql-error.log under /var/lib/mysql/data and found it throws an error: Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist
I tried to run mysql_install_db –-user=mysql –-ldata=/var/lib/mysql/data under root. But it failed with the error like:
/usr/sbin/mysqld: Too many arguments (first extra is '–-user=mysql').
Use --verbose --help to get a list of available options
130508 16:30:57 [ERROR] Aborting
Drives me crazy...
Is there anything in /var/lib/mysql/data currently? If so (and assuming you never put any data in there you are trying to save), remove whatever is in that folder. Then as root (or with sudo) try to run your command again:
The log bits you posted are just warnings, so those should not affect this.
Are you attempting to shutdown the server using root/sudo? If not, try again with “sudo /etc/init.d/mysql stop”. If that does not work, try “sudo mysqladmin shutdown”. If neither of those work, kill the process (kill mysqld_safe one first and then mysqld), and then attempt to start / stop it again. If none of that works, post up the exact commands you ran and your error log (/var/lib/mysql/data/mysql-error.log)
[QUOTE=scott.nemes;10053]
The log bits you posted are just warnings, so those should not affect this. Are you attempting to shutdown the server using root/sudo? If not, try again with “sudo /etc/init.d/mysql stop”. If that does not work, try “sudo mysqladmin shutdown”. If neither of those work, kill the process (kill mysqld_safe one first and then mysqld), and then attempt to start / stop it again. If none of that works, post up the exact commands you ran and your error log (/var/lib/mysql/data/mysql-error.log)
Thank for your help scott
I) I tried “sudo /etc/init.d/mysql stop”, it’s not working. ( although it shows * Stopping MySQL (Percona Server) mysqld [OK] )
“sudo mysqladmin shutdown”, it gives this error:
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)'
Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!
3) Then I manually kill these 2 processes. And then run "sudo /etc/init.d/mysql start", it shows " * Starting MySQL (Percona Server) database server mysqld [fail]"
But it did start the server! Below is the copy from error.log, I didnt see any error. Really don't know why it says fail, but actually start it.
130508 18:54:11 InnoDB: Waiting for the background threads to start
130508 18:54:12 Percona XtraDB (http://www.percona.com) 5.5.30-rel30.2 started; log sequence number 1597945
130508 18:54:12 [Note] Recovering after a crash using /var/lib/mysql/data/mysql-bin
130508 18:54:12 [Note] Starting crash recovery...
130508 18:54:12 [Note] Crash recovery finished.
130508 18:54:12 [Warning] 'user' entry 'root@find-rent' ignored in --skip-name-resolve mode.
130508 18:54:12 [Warning] 'proxies_priv' entry '@ root@find-rent' ignored in --skip-name-resolve mode.
130508 18:54:12 [Note] Event Scheduler: Loaded 0 events
130508 18:54:12 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.30-30.2-log' socket: '/var/lib/mysql/data/mysql.sock' port: 3306 Percona Server (GPL), Release 30.2
Try commenting out the “skip_name_resolve” line in your my.cnf and then kill the processes again and start MySQL back up. I just noticed that it is complaining about your root user, so that actually could be the issue.
I just notice one thing. Before the debian-start and debian.cnf are all empty. But now they have data, I dont know if that has something to do with it
debian-start
#!/bin/bash
#
# This script is executed by "/etc/init.d/mysql" on every (re)start.
#
# Changes to this file will be preserved when updating the Debian package.
#
PERCONA_PREFIX=/usr
source "${PERCONA_PREFIX}"/share/mysql/debian-start.inc.sh
MYSQL="${PERCONA_PREFIX}/bin/mysql --defaults-file=/etc/mysql/debian.cnf"
MYADMIN="${PERCONA_PREFIX}/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf"
MYUPGRADE="${PERCONA_PREFIX}/bin/mysql_upgrade --defaults-extra-file=/etc/mysql/debian.cnf"
MYCHECK="${PERCONA_PREFIX}/bin/mysqlcheck --defaults-file=/etc/mysql/debian.cnf"
MYCHECK_SUBJECT="WARNING: mysqlcheck has found corrupt tables"
MYCHECK_PARAMS="--all-databases --fast --silent"
MYCHECK_RCPT="root"
# The following commands should be run when the server is up but in background
# where they do not block the server start and in one shell instance so that
# they run sequentially. They are supposed not to echo anything to stdout.
# If you want to disable the check for crashed tables comment
# "check_for_crashed_tables" out.
# (There may be no output to stdout inside the background process!)
echo "Checking for corrupt, not cleanly closed and upgrade needing tables."
(
upgrade_system_tables_if_necessary;
check_root_accounts;
check_for_crashed_tables;
) >&2 &
exit 0
debian.cnf:
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host = localhost
user = debian-sys-maint
password = Jfa78OfLJwc8ADvE
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host = localhost
user = debian-sys-maint
password = Jfa78OfLJwc8ADvE
socket = /var/run/mysqld/mysqld.sock
basedir = /usr
Could just be an issue with authentication due to the “fun” way Debian uses MySQL. Debian uses the user “debian-sys-maint” to control the MySQL instance. When you moved the data directory and re-created the MySQL system tables, that user likely was not created and/or has a different password. So the easiest way to try and fix it would be to copy all of the files / directories from the original MySQL data directory into the new data directory. That will give you the original users that Debian setup to control MySQL, and will likely fix the issue.
So if you still have the original data directory contents, I’d do the following:
Kill the current MySQL procesesses again (mysqld_safe then mysqld)
Remove the contents of the new data directory (all sub directories as well)
Copy the contents of the original data directory to the new data directory (make sure to use the recursive flag, i.e. cp -r)
Make sure the ownership of the new data dir and contents is still correct
Remove the ib_logfiles (likely ib_logfile0 and ib_logfile1) from the new data directory
If there is a socket file (*.sock) in the new data directory, remove it
Start up MySQL again
If you do not have the original data directory contents, we can re-create the user, but that is more involved, so easier to avoid that if possible since this is a fresh install.
it’s still failed. There is no error in the log file. Only two warning: