MySQL Tips&Tricks

set root password

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

grant access to a remote machine

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

create user with a dedicated database

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

query cache

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

one innodb file per table

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

note: this will work only for databases created after you enable this option. for this reason it's always better to activate it at the very begining of the server's setup (i.e. before creating or importing any database, or you'll have to dump and reimport all your innodb databases for this to take effect.

replication

replication account creation

  • 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';

database(s) backup

  • 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 (remember to use it on the “slave” only, because 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 " ---> backuping ${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

slave setup

  • 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

    mysql> CHANGE MASTER TO
    ->     MASTER_HOST='master_host_name',
    ->     MASTER_USER='master_user_name',
    ->     MASTER_PASSWORD='slavepass',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;

  • Execute START SLAVE on the slave.

optimization

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

troubleshooting

duplicate entry in replication

  • stop slave

mysql> STOP SLAVE;

  • either manually delete the duplicate entry, or skip counter by issuing:

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

  • start slave

mysql> START SLAVE;

rebuild database indexes

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

reset root password

  • 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;