mysql database backup and optimizing

Backup database

It is recommended to use mysqldump to backup, instead of duplicating the data directory directly. Frequently, we can set a schedule to complete this task by crontab. Here is an example:

15 4 * * * /home/mysql/bin/mysqldump -S/data/app_1/mysql.sock -umysql db_name | gzip -f>/path/to/backup/db_name.`data +\%w`.dump.gz

Some notes:

1. In the crontab, '%' should be converted into '\%'.
2. Choose a proper time to do backup tasks, such as 4:00~6:00 am, depended by your log statistics.

You can backup firstly at your local host, and then transfer into your remote backup server. You can also backup at a remote server directly,and your command will become:

/home/mysql/bin/mysqldump -h mysql_database_ip -umysql db_name | gzip -f>/path/to/backup/db_name.`data +\%w`.dump.gz

Example to optimize a forum

1. Use Webalizer to replace the database statistics.
2. Firstly, Use the command "top" to check the load of cup and memory. For example the load of cup is 80%, and memory is 10MB, which means the index cache of database has exhausted. So, modified the startup arguments, add "-O key_buffer=32", such as:

   /home/mysql/bin/safe_mysqld --user=mysql --pid-file=/path/to/mysql.pid \
   --datadir=/path/to/data --port=3402 --socket="$rundir"/mysql.sock \
   -O max_connections=500 -O wait_timeout=600 -O key_buffer=32M &
   

   Wait a few minutes, until the database runs steadily. And check the load of cpu and memory again. Assign more memory for index caches, until the load cpu decreases below 10%. For a database application, it takes more effects to assign spare memory to database other than web application, because faster mysql query processes will accelerate the web application and save the resources of concurrency web services.

3. Run the command "show processlist" to stat the most frequent sql query statment. Run "show processlist" every minute by crontab, and log the results.

   * * * * * (mysql -uuser -ppassword < ~/show_processlist.sql >> ~/mysql_processlist.log)
   

   The content of show_processlist.sql contains only one command: show processlist; For example, filter the log and find the statements containing "where":

   grep where mysql_processlist.log
   

   If death locks exist, review the bad sql design. If search processes are slow, index the fields of "where" statements. If order processes are slow, index the fields of "order by" statements. If the query processes containing "%like%" are slow, recommend to deny them or find other text indexing method.

4. If some databases are extremely frequently used, try to assgin different ports for each database.

Compile and install

Some tips:

• Install the stable version as last as possible,
• Donot compile with the option "--with-charset=xxx", which will make trouble.
• Donot compile with innodb, which is often used in the support of enterprise, and slower than MYISAM.

  ./configure --prefix=/home/mysql --without-innodb
  make
  make install
  

  Reference: http://www.chedong.com/tech/mysql.html