Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision
Previous revision
public:mysql [2011/08/06 17:32] – [one innodb file per table] Nicopublic:mysql [2022/08/04 15:35] (current) – fix indent Nico
Line 12: Line 12:
 <code sql> <code sql>
 mysql> GRANT ALL PRIVILEGES ON *.* TO 'user'@'client' IDENTIFIED BY 'password' WITH GRANT OPTION; 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> </code>
  
Line 18: Line 24:
 you can see the query cache statistics at any time by issuing: you can see the query cache statistics at any time by issuing:
  
-<code>+<code sql>
 mysql> SHOW VARIABLES LIKE '%query_cache%'; mysql> SHOW VARIABLES LIKE '%query_cache%';
 </code> </code>
Line 28: Line 34:
 <code> <code>
 query-cache-type = 1 query-cache-type = 1
-query-cache-size = 20M+query-cache-size = 32M
 </code> </code>
  
Line 46: Line 52:
  
   * Set up an account on the master server that the slave server can use to connect   * Set up an account on the master server that the slave server can use to connect
 +
 <code sql> <code sql>
 mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave' IDENTIFIED BY 'slavepass'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave' IDENTIFIED BY 'slavepass';
Line 54: Line 61:
  
   * Flush all the tables and block write statements   * Flush all the tables and block write statements
 +
 <code sql> <code sql>
 mysql> FLUSH TABLES WITH READ LOCK; mysql> FLUSH TABLES WITH READ LOCK;
 </code> </code>
 +
   * use tar to create an archive that includes all databases   * use tar to create an archive that includes all databases
 +
 <code bash># tar -cvf /tmp/mysql-snapshot.tar .</code> <code bash># tar -cvf /tmp/mysql-snapshot.tar .</code>
 +
   * read the value of the current binary log name and offset on the master:    * read the value of the current binary log name and offset on the master: 
 +
 <code sql> <code sql>
 mysql > SHOW MASTER STATUS; mysql > SHOW MASTER STATUS;
Line 68: Line 80:
 +---------------+----------+--------------+------------------+ +---------------+----------+--------------+------------------+
 </code> </code>
 +
   * Release the lock   * Release the lock
 +
 <code sql> <code sql>
 mysql> UNLOCK TABLES; mysql> UNLOCK TABLES;
 </code> </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 ==== ==== slave setup ====
Line 82: Line 130:
 report-host = slave_name  report-host = slave_name 
 </code> </code>
 +
   * unpack the snapshot   * unpack the snapshot
   * Execute the following statement on the slave    * Execute the following statement on the slave 
-<code>+<code sql>
     mysql> CHANGE MASTER TO     mysql> CHANGE MASTER TO
     ->     MASTER_HOST='master_host_name',     ->     MASTER_HOST='master_host_name',
Line 92: Line 141:
     ->     MASTER_LOG_POS=recorded_log_position;     ->     MASTER_LOG_POS=recorded_log_position;
 </code> </code>
 +
   * Execute START SLAVE on the slave.   * 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 ===== ===== troubleshooting =====
Line 100: Line 161:
  
   * stop slave   * stop slave
-<code>+<code sql>
 mysql> STOP SLAVE; mysql> STOP SLAVE;
 </code> </code>
   * either manually delete the duplicate entry, or skip counter by issuing:   * either manually delete the duplicate entry, or skip counter by issuing:
-<code>+<code sql>
 mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
 </code> </code>
   * start slave   * start slave
-<code>+<code sql>
 mysql> START SLAVE; mysql> START SLAVE;
 </code> </code>
  
 ==== rebuild database indexes ==== ==== rebuild database indexes ====
-<code> +<code bash
-  # mysqlcheck -p -r -q -q <table>+# mysqlcheck -p -r -q -q <table>
 </code> </code>
  
Line 121: Line 182:
  
   * start MySQL without authentification   * start MySQL without authentification
-<code>+ 
 +<code bash>
 # /usr/local/libexec/mysqld -u mysql --skip-grant # /usr/local/libexec/mysqld -u mysql --skip-grant
 </code> </code>
 +
   * connect to MySQL and change password   * connect to MySQL and change password
-<code>+ 
 +<code sql>
 # mysql # mysql
   Welcome to the MySQL monitor.  Commands end with ; or \g.   Welcome to the MySQL monitor.  Commands end with ; or \g.