MySQL Tips&Tricks

shell> mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('password');
mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('password');

mysql> GRANT ALL PRIVILEGES ON *.* TO 'user'@'client' IDENTIFIED BY 'password' WITH GRANT OPTION;

mysql> CREATE DATABASE db;
mysql> GRANT ALL PRIVILEGES ON db.* TO 'user'@'client' IDENTIFIED BY 'password';

you can see the query cache statistics at any time by issuing:

mysql> SHOW VARIABLES LIKE '%query_cache%';

if all values are 0, the query cache is not working.

enable it by setting the cache size in my.cnf:

query-cache-type = 1
query-cache-size = 32M

by default, innodb use a gigantic “ibdata” file where it stores all the databases and tables; but you can have it the nice myisam-way, i.e. one directory for each database, and one or several files per table. just add this line to my.cnf:

innodb_file_per_table

• Set up an account on the master server that the slave server can use to connect

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave' IDENTIFIED BY 'slavepass';

• Flush all the tables and block write statements

mysql> FLUSH TABLES WITH READ LOCK;

• use tar to create an archive that includes all databases

# tar -cvf /tmp/mysql-snapshot.tar .

• read the value of the current binary log name and offset on the master:

mysql > SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File          | POSITION | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73       | test         | manual,mysql     |
+---------------+----------+--------------+------------------+

• Release the lock

mysql> UNLOCK TABLES;

• Here is a backup script you can use (on a daily use, remember to use it on the “slave” only, especially if you use MyISAM engine: tables locking will cause your database to be unavailable for writing):

#!/bin/sh
 
BACKUPDIR=/home/mysql_backup
LOGFILE=${BACKUPDIR}/my_dump_mysql.log
SENDNSCA=/usr/local/sbin/send_nsca\ -H\ ${NAGIOS_HOST}\ -d\ ';;'\ -c\ /usr/local/etc/nagios/send_nsca.cfg
HOSTNAME=`/bin/hostname -f | /usr/bin/cut -d '.' -f 1,2`
backup_success=1
 
mkdir -p ${BACKUPDIR}
rm -f ${LOGFILE};
 
for i in `echo 'show databases;' | mysql | grep -v Database`;
do
  echo -n " ---> backing up ${i}..." >> ${LOGFILE}
  mysqldump --add-drop-table --add-locks --create-options --disable-keys --extended-insert --single-transaction\
    --databases --lock-tables --quick --set-charset $i > ${BACKUPDIR}/$i.sql 2>> ${LOGFILE};
  if [ $? -eq 0 ];
  then
    echo " OK" >> ${LOGFILE};
  else
    backup_success=0
  fi
done
 
if [ $backup_success -eq 1 ]
then
        echo "${HOSTNAME};;mysql_backups;;0;OK - backup succeeded at `date`" | ${SENDNSCA} >/dev/null
else
        echo "${HOSTNAME};;mysql_backups;;2;CRITICAL - backup failed at `date`, please check ${LOGFILE} for complete report." | ${SENDNSCA} >/dev/null
fi

• copy and extract database backup on the slave
• add the following lines to slave's my.cnf file

[mysqld]
server-id   = slave_id
report-host = slave_name 

• unpack the snapshot
• Execute the following statement on the slave <code sql> mysql> CHANGE MASTER TO

  1. > MASTERHOST='masterhostname', → MASTERUSER='masterusername',
  2. > MASTERPASSWORD='slavepass', → MASTERLOGFILE='recordedlogfilename',
  3. > MASTERLOGPOS=recordedlogposition;

  </code>

• Execute START SLAVE on the slave.

MySQLTuner is a great tool to start with. You can find some good tips on this page (in French).

If MySQLTuner reports fragmented tables, you can check them all with:

mysqlcheck -o --all-databases

• stop slave <code sql> mysql> STOP SLAVE; </code>
• either manually delete the duplicate entry, or skip counter by issuing: <code sql> mysql> SET GLOBAL SQLSLAVESKIP_COUNTER = 1; </code>
• start slave <code sql> mysql> START SLAVE; </code>

# mysqlcheck -p -r -q -q <table>

• start MySQL without authentification

# /usr/local/libexec/mysqld -u mysql --skip-grant

• connect to MySQL and change password

# mysql
  Welcome TO the MySQL monitor.  Commands END WITH ; OR \g.
  Your MySQL connection id IS 62 TO server version: 4.0.20
 
  TYPE 'help;' OR '\h' FOR help. TYPE '\c' TO clear the buffer.
 
mysql> DELETE FROM mysql.user WHERE USER='root';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;