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 [2022/07/19 23:25] – [replication account creation] typo Nicopublic:mysql [2022/08/04 15:35] (current) – fix indent Nico
Line 61: 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 75: 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 96: 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 114: 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 122: 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 
Line 132: 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 140: 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 162: Line 174:
  
 ==== rebuild database indexes ==== ==== rebuild database indexes ====
-<code> +<code bash> 
-  # mysqlcheck -p -r -q -q <table>+# mysqlcheck -p -r -q -q <table>
 </code> </code>
  
Line 170: 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 sql> <code sql>
 # mysql # mysql