Linux Ask! is a Q & A web site specific for Linux related questions. Questions are collected, answered and audited by experienced Linux users.
How to change the MySQL temporary directory
Answer:
To change the temporary directory of your MySQL server to use a non-default directory, you can change the tmpdir parameter in your my.cnf
..
tmpdir = /data/tmp
..Don't forget to change the permission of that temporary directory
# sudo chmod mysql:mysql /data/tmp
Then restart MySQL to take effect.
# /sbin/service mysqld restart
Alter all tables in MySQL
Answer:
To alter all the tables in a given database in MySQL, e.g. change the storage engine, you can first generate the SQL statements using following method.
mysql> select CONCAT("Alter Table `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` ENGINE = InnoDB") AS cmd from TABLES where TABLE_SCHEMA = "test";
It will print out the followings:
+----------------------------------------------------------------+
| cmd |
+----------------------------------------------------------------+
| Alter Table `test`.`foo` ENGINE = InnoDB |
| Alter Table `test`.`bar` ENGINE = InnoDB |
...
Then you can execute the above commands manually.
Auto remove expired binary log in MySQL
Answer:
If you have enabled the binary log in your MySQL server, you need to be aware that the log will use up the space of your local disk space. Old binary logs are generally not needed and you can auto remove expired entries from the binary log.
To do so, edit the MySQL configurations, e.g. /etc/my.cnf
..
expire_logs_days = 60
..The above configuration will auto remove entries in the binary old if they are older than 60 days.
Don't forget to restart MySQL to take effect.
# /sbin/service mysqld restart
Updated the Slave binary log if the updates are received from a MySQL master
Answer:
When the updates are received from a MySQL master to a slave, it will not be logged in the binary log of the slave server. Sometimes, you might want to enable it, for example, in order to setup a chained replication.
To do so, edit the MySQL configurations, e.g. /etc/my.cnf
..
log-slave-updates
..Don't forget to restart MySQL to take effect.
# /sbin/service mysqld restart
Perform XtraBackup and zip to a file
Answer:
In the previous article, we have discussed how to perform a hot backup on InnoDB with XtraBackup , but is it possible to zip the result directly to a file?
Sure, you can use the wrapper script came with the XtraBackup - innobackupex
For example, to backup and zip the result files to zipped files, you can use:
# innobackupex --stream=tar ./ | gzip - > backup.tar.gz
