While installing MySQL Server it is always good to keep in mind that the logs and data folders will potentially have a big size. By default MySQL keeps them in the root mount point (i.e. ‘/’). That may cause your database server system disk to get full, which is never a good idea.
This article describes how to move these two folders to ‘/home’ which is ideally mounted into another disk and has enough space to keep your database data and logs.
First, I install the required apt-get packages as follows:
apt-get update apt-get install mysql-server
To check the status:
/etc/init.d/mysql status /usr/bin/mysqladmin Ver 8.41 Distrib 5.0.51a, for debian-linux-gnu on i486 Copyright (C) 2000-2006 MySQL AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 5.0.51a-24 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 3 sec Threads: 1 Questions: 78 Slow queries: 0 Opens: 23 Flush tables: 1 Open tables: 17 Queries per second avg: 26.000.
Now, stop MySQL, move the folders to the right location, reconfigure MySQL and start again:
# Stop MySQL /etc/init.d/mysql stop # Move and reconfigure data mkdir /home/mysql mv /var/lib/mysql /home/mysql/mysql-data ln -s /home/mysql/mysql-data/ /var/lib/mysql # Move and reconfigure logs mv /var/log/mysql/ /home/mysql/mysql-logs ln -s /home/mysql/mysql-logs/ /var/log/mysql # Start MySQL and check that everything is OK /etc/init.d/mysql start /etc/init.d/mysql status /usr/bin/mysqladmin Ver 8.41 Distrib 5.0.51a, for debian-linux-gnu on i486 Copyright (C) 2000-2006 MySQL AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 5.0.51a-24 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 14 sec Threads: 1 Questions: 78 Slow queries: 0 Opens: 23 Flush tables: 1 Open tables: 17 Queries per second avg: 5.571.
These are some settings that I usually put on the /etc/mysql/my.cnf configuration file:
# Here you can see queries with especially long duration log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 1 log-queries-not-using-indexes # A server-id unique server-id = 177 log-bin = /var/log/mysql/mysql-bin.log log-bin-index = /var/log/mysql/mysql-bin.log innodb_file_per_table # Unique log names (this prevents replication breaking upon hostname change :-) relay-log = iamalsounique98127-relay-bin relay-log-index = iamalsounique98127-relay-bin # Taking care of the auto-increment values (for multi-master replication) auto_increment_increment = 10 auto_increment_offset = 1
For these changes to take effect, you would need to restart MySQL:
/etc/init.d/mysql restart
If you want to ignore databases or tables you may use the following options:
binlog_ignore_db = information_schema replicate_ignore_db = information_schema binlog_ignore_db = mysql replicate_ignore_db = mysql # Ignore all the cache* tables which have caused DUPLICATE # ENTRY issues. Unai. replicate_wild_ignore_table = exampledb.cache%
Having ‘binlog_ignore_db’ is enough to exclude databases from replication BUT having ‘replicate_ignore_db’ as well will make things clearer since the databases that are being ignored will appear in both the ‘SHOW SLAVE STATUSG’ and ‘SHOW MASTER STATUSG’.