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:28] – [database(s) backup] some color for shell script Nicopublic:mysql [2026/04/05 11:06] (current) – [duplicate entry in replication] cosmetic fixes Nico
Line 87: Line 87:
 </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 bash> <code bash>
Line 103: 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 121: 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 129: 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 139: 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 147: 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 156: Line 161:
  
   * stop slave   * stop slave
-<code sql>+ 
 +```sql
 mysql> STOP SLAVE; mysql> STOP SLAVE;
-</code>+``` 
   * either manually delete the duplicate entry, or skip counter by issuing:   * either manually delete the duplicate entry, or skip counter by issuing:
-<code sql>+ 
 +```sql
 mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
-</code>+``` 
   * start slave   * start slave
-<code sql>+ 
 +```sql
 mysql> START SLAVE; mysql> START SLAVE;
-</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 177: Line 187:
  
   * 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