====== MySQL Tips&Tricks ======

===== set root password =====
<code sql>
shell> mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('password');
mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('password');
</code>


===== grant access to a remote machine =====
<code sql>
mysql> GRANT ALL PRIVILEGES ON *.* TO 'user'@'client' IDENTIFIED BY 'password' WITH GRANT OPTION;
</code>

===== create user with a dedicated database =====
<code sql>
mysql> CREATE DATABASE db;
mysql> GRANT ALL PRIVILEGES ON db.* TO 'user'@'client' IDENTIFIED BY 'password';
</code>

===== query cache =====

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

<code sql>
mysql> SHOW VARIABLES LIKE '%query_cache%';
</code>

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

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

<code>
query-cache-type = 1
query-cache-size = 32M
</code>

===== 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:

<code>
innodb_file_per_table
</code>

<note>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.
</note>
===== replication =====

==== replication account creation ====

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

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


==== database(s) backup ====

  * Flush all the tables and block write statements

<code sql>
mysql> FLUSH TABLES WITH READ LOCK;
</code>

  * use tar to create an archive that includes all databases

<code bash># tar -cvf /tmp/mysql-snapshot.tar .</code>

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

<code sql>
mysql > SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73       | test         | manual,mysql     |
+---------------+----------+--------------+------------------+
</code>

  * Release the lock

<code sql>
mysql> UNLOCK TABLES;
</code>

  * 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):

<code bash>
#!/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
</code>

==== slave setup ====
  * copy and extract database backup on the slave
  * add the following lines to slave's my.cnf file 
<code>
[mysqld]
server-id   = slave_id
report-host = slave_name 
</code>

  * unpack the snapshot
  * Execute the following statement on the slave 
<code sql>
    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;
</code>

  * Execute START SLAVE on the slave.


===== optimization =====

 [[https://github.com/major/MySQLTuner-perl|MySQLTuner]] is a great tool to start with. You can find some good tips on [[http://wiki.deimos.fr/index.php?title=MysqlTuner_:_Optimiser_votre_serveur_MySQL|this page]] (in French).

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

<code bash>
mysqlcheck -o --all-databases
</code>

===== troubleshooting =====


==== duplicate entry in replication ====

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

==== rebuild database indexes ====
<code bash>
# mysqlcheck -p -r -q -q <table>
</code>


==== reset root password ====

  * start MySQL without authentification

<code bash>
# /usr/local/libexec/mysqld -u mysql --skip-grant
</code>

  * connect to MySQL and change password

<code sql>
# 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;
</code>