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 [2013/09/13 22:49] – [optimization] defrag 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 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 (remember to use it on the "slave" only, because tables locking will cause your database to be unavailable for writing):+  * 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>+<code bash>
 #!/bin/sh #!/bin/sh
  
Line 89: Line 103:
 for i in `echo 'show databases;' | mysql | grep -v Database`; for i in `echo 'show databases;' | mysql | grep -v Database`;
 do do
-  echo -n " ---> backuping ${i}..." >> ${LOGFILE}+  echo -n " ---> backing up ${i}..." >> ${LOGFILE}
   mysqldump --add-drop-table --add-locks --create-options --disable-keys --extended-insert --single-transaction\   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};     --databases --lock-tables --quick --set-charset $i > ${BACKUPDIR}/$i.sql 2>> ${LOGFILE};
Line 107: Line 121:
 fi fi
 </code> </code>
 +
 ==== slave setup ==== ==== slave setup ====
   * copy and extract database backup on the slave   * copy and extract database backup on the slave
Line 115: 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 125: 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.
  
Line 133: Line 150:
  
  If MySQLTuner reports fragmented tables, you can check them all with:  If MySQLTuner reports fragmented tables, you can check them all with:
-<code>+ 
 +<code bash>
 mysqlcheck -o --all-databases mysqlcheck -o --all-databases
 </code> </code>
 +
 ===== troubleshooting ===== ===== troubleshooting =====
  
Line 142: 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 163: 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.