Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionNext revisionBoth sides next revision | ||
public:mysql [2008/01/27 11:36] – Nico | public:mysql [2022/07/19 23:32] – [database(s) backup] typo Nico | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ====== set root password | + | ====== MySQL Tips& |
- | < | + | |
+ | ===== set root password ===== | ||
+ | < | ||
shell> mysql -u root | shell> mysql -u root | ||
mysql> SET PASSWORD FOR ' | mysql> SET PASSWORD FOR ' | ||
Line 7: | Line 9: | ||
- | ====== grant access to a remote machine | + | ===== grant access to a remote machine ===== |
<code sql> | <code sql> | ||
mysql> GRANT ALL PRIVILEGES ON *.* TO ' | mysql> GRANT ALL PRIVILEGES ON *.* TO ' | ||
</ | </ | ||
- | ====== replication ====== | + | ===== create user with a dedicated database |
+ | <code sql> | ||
+ | mysql> CREATE DATABASE db; | ||
+ | mysql> GRANT ALL PRIVILEGES ON db.* TO ' | ||
+ | </ | ||
- | ===== replication account creation | + | ===== query cache ===== |
+ | |||
+ | you can see the query cache statistics at any time by issuing: | ||
+ | |||
+ | <code sql> | ||
+ | mysql> SHOW VARIABLES LIKE ' | ||
+ | </ | ||
+ | |||
+ | if all values are 0, the query cache is not working. | ||
+ | |||
+ | enable it by setting the cache size in my.cnf: | ||
+ | |||
+ | < | ||
+ | query-cache-type = 1 | ||
+ | query-cache-size = 32M | ||
+ | </ | ||
+ | |||
+ | ===== one innodb file per table ===== | ||
+ | |||
+ | by default, innodb use a gigantic " | ||
+ | |||
+ | < | ||
+ | innodb_file_per_table | ||
+ | </ | ||
+ | |||
+ | < | ||
+ | </ | ||
+ | ===== replication ===== | ||
+ | |||
+ | ==== replication account creation ==== | ||
* 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 ' | mysql> GRANT REPLICATION SLAVE ON *.* TO ' | ||
</ | </ | ||
- | ===== database(s) backup | + | |
+ | ==== database(s) backup ==== | ||
* Flush all the tables and block write statements | * Flush all the tables and block write statements | ||
- | < | + | |
+ | < | ||
+ | mysql> FLUSH TABLES WITH READ LOCK; | ||
+ | </ | ||
* use tar to create an archive that includes all databases | * use tar to create an archive that includes all databases | ||
- | < | + | |
+ | < | ||
* 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: | ||
- | < | + | |
+ | < | ||
mysql > SHOW MASTER STATUS; | mysql > SHOW MASTER STATUS; | ||
+---------------+----------+--------------+------------------+ | +---------------+----------+--------------+------------------+ | ||
Line 36: | Line 80: | ||
+---------------+----------+--------------+------------------+ | +---------------+----------+--------------+------------------+ | ||
</ | </ | ||
+ | |||
* Release the lock | * Release the lock | ||
- | < | + | |
- | ===== slave setup ===== | + | < |
+ | mysql> UNLOCK TABLES; | ||
+ | </ | ||
+ | |||
+ | * Here is a backup script you can use (remember to use it on the " | ||
+ | |||
+ | <code bash> | ||
+ | #!/bin/sh | ||
+ | |||
+ | BACKUPDIR=/ | ||
+ | LOGFILE=${BACKUPDIR}/ | ||
+ | SENDNSCA=/ | ||
+ | HOSTNAME=`/ | ||
+ | backup_success=1 | ||
+ | |||
+ | mkdir -p ${BACKUPDIR} | ||
+ | rm -f ${LOGFILE}; | ||
+ | |||
+ | for i in `echo 'show databases;' | ||
+ | do | ||
+ | echo -n " ---> backing up ${i}..." | ||
+ | mysqldump --add-drop-table --add-locks --create-options --disable-keys --extended-insert --single-transaction\ | ||
+ | --databases --lock-tables --quick --set-charset $i > ${BACKUPDIR}/ | ||
+ | if [ $? -eq 0 ]; | ||
+ | then | ||
+ | echo " OK" >> ${LOGFILE}; | ||
+ | else | ||
+ | backup_success=0 | ||
+ | fi | ||
+ | done | ||
+ | |||
+ | if [ $backup_success -eq 1 ] | ||
+ | then | ||
+ | echo " | ||
+ | else | ||
+ | echo " | ||
+ | fi | ||
+ | </ | ||
+ | ==== slave setup ==== | ||
* copy and extract database backup on the slave | * copy and extract database backup on the slave | ||
* add the following lines to slave' | * add the following lines to slave' | ||
< | < | ||
[mysqld] | [mysqld] | ||
- | server-id=slave_id | + | server-id |
+ | report-host = slave_name | ||
</ | </ | ||
* unpack the snapshot | * unpack the snapshot | ||
* Execute the following statement on the slave | * Execute the following statement on the slave | ||
- | < | + | < |
+ | | ||
-> | -> | ||
-> | -> | ||
Line 55: | Line 140: | ||
</ | </ | ||
* Execute START SLAVE on the slave. | * Execute START SLAVE on the slave. | ||
- | ====== troubleshooting ====== | ||
- | ===== duplicate entry in replication | + | ===== optimization |
- | * stop slave | + | |
+ | |||
+ | If MySQLTuner reports fragmented tables, you can check them all with: | ||
< | < | ||
+ | mysqlcheck -o --all-databases | ||
+ | </ | ||
+ | ===== troubleshooting ===== | ||
+ | |||
+ | |||
+ | ==== duplicate entry in replication ==== | ||
+ | |||
+ | * stop slave | ||
+ | <code sql> | ||
mysql> STOP SLAVE; | mysql> STOP SLAVE; | ||
</ | </ | ||
* either manually delete the duplicate entry, or skip counter by issuing: | * either manually delete the duplicate entry, or skip counter by issuing: | ||
- | < | + | < |
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; | mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; | ||
</ | </ | ||
* start slave | * start slave | ||
- | < | + | < |
mysql> START SLAVE; | mysql> START SLAVE; | ||
</ | </ | ||
- | ===== rebuild database indexes | + | ==== rebuild database indexes ==== |
< | < | ||
# mysqlcheck -p -r -q -q < | # mysqlcheck -p -r -q -q < | ||
Line 79: | Line 174: | ||
- | ===== reset root password | + | ==== reset root password ==== |
* start MySQL without authentification | * start MySQL without authentification | ||
Line 86: | Line 181: | ||
</ | </ | ||
* connect to MySQL and change password | * connect to MySQL and change password | ||
- | < | + | < |
# mysql | # mysql | ||
Welcome to the MySQL monitor. | Welcome to the MySQL monitor. |