Linux Ask!

Linux Ask! is a Q & A web site specific for Linux related questions. Questions are collected, answered and audited by experienced Linux users.

Jul 172011
 

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

Jul 132011
 

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.

Jul 052011
 

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

Jun 252011
 

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

Jun 072011
 

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