MySQL - Database dual-machine hot standby configuration method and code examples

Mysql database does not have an incremental backup mechanism. When the amount of data is too large, backup is a big problem. Fortunately, the MySQL database provides a master-slave backup mechanism, which actually writes all the data in the master database to the backup database at the same time.

1. Description

1. MySQL database does not have an incremental backup mechanism. When the amount of data is too large, backup is a big problem. Fortunately, the MySQL database provides a master-slave backup mechanism, which actually writes all the data in the master database to the backup database at the same time. Implement hot backup of mysql database.
2. To implement dual-machine hot backup, you must first understand the version requirements of the master-slave database server. To achieve hot standby, the MySQL version must be higher than 3.2. Another basic principle is that the database version of the slave database can be higher than the master server database version, but it cannot be lower than the master server database version.

2. Configuration

1. Configure the main server

a. Check the version

First check whether the version of the main server is Support hot standby version. Then check whether the configuration of the mysqld configuration block in my.cnf (unix-like) or my.ini (windows) has log-bin (recording database change log). Because the replication mechanism of mysql is a log-based replication mechanism, the main server must It requires support for change logs. Then set the database to which the log should be written or the database where the log should not be written. This way only changes to the database that interest you are written to the database's log.

server-id=1 //数据库的id这个应该默认是1就不用改动
log-bin=log_name //日志文件的名称，这里可以制定日志到别的目录 如果没有设置则默认主机名的一个日志名称
binlog-do-db=db_name //记录日志的数据库
binlog-ignore-db=db_name //不记录日志的数据库

If there are multiple databases above, use "," to separate them
Then set the user account for synchronizing the database

mysql> GRANT REPLICATION SLAVE ON *.*
-> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

Versions before 4.0.2, because it is not supported REPLICATION To achieve this function, use the following statement

mysql> GRANT FILE ON *.*
-> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

Restart the database after setting the configuration file of the main server

b. Lock the existing database and back it up now The data

Lock the database

mysql> FLUSH TABLES WITH READ LOCK;

There are two ways to back up the database. One is to directly enter the data directory of mysql and then package the folder you need to back up the database. The second is Use mysqldump to back up the database but add the "--master-data" parameter. It is recommended to use the first method to back up the database
c. Check the status of the master server

mysql> show master status\G;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73 | test | manual,mysql |
+---------------+----------+--------------+------------------+

Record the values ​​of the File and Position items for later use.
d. Then open the database lock

mysql> UNLOCK TABLES;

4. Configure the slave server

a. First set the database configuration file

server-id=n //设置数据库id默认主服务器是1可以随便设置但是如果有多台从服务器则不能重复。
master-host=db-master.mycompany.com //主服务器的IP地址或者域名
master-port=3306 //主数据库的端口号
master-user=pertinax //同步数据库的用户
master-password=freitag //同步数据库的密码
master-connect-retry=60 //如果从服务器发现主服务器断掉，重新连接的时间差
report-host=db-slave.mycompany.com //报告错误的服务器

b. Import the database backed up from the master database server into the slave server

c. Start the slave server Database

Then start the slave database server. If the "--skip-slave-start" parameter is not added when starting, it will enter mysql

mysql> slave stop; //停止slave的服务

d. Set the master Various parameters of the server

mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host_name', //主服务器的IP地址
-> MASTER_PORT=3306, //主服务器端口
-> MASTER_USER='replication_user_name', //同步数据库的用户
-> MASTER_PASSWORD='replication_password', //同步数据库的密码
-> MASTER_LOG_FILE='recorded_log_file_name', //主服务器二进制日志的文件名(前面要求记住的参数)
-> MASTER_LOG_POS=recorded_log_position; //日志文件的开始位置(前面要求记住的参数)

e. Start the thread to synchronize the database

mysql> slave start;

Check the synchronization of the database. If the synchronization can be successful, congratulations!
Check the status of the master-slave server

mysql> SHOW PROCESSLIST\G //可以查看mysql的进程看看是否有监听的进程

3. Clear the log

If the log is too large, the steps to clear the log are as follows:

1. Lock the master database

mysql> FLUSH TABLES WITH READ LOCK;

2. Stop the slave from the database

mysql> slave stop;

3. Check the log file name of the main database and the position of the log file

show master status;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_do_db | Binlog_ignore_db |
+---------------+----------+--------------+------------------+
| louis-bin.001 | 79 | | mysql |
+---------------+----------+--------------+------------------+

4. Unlock the main database

mysql> UNLOCK TABLES;

5. Update the information of the master database in the slave database

mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host_name', //主服务器的IP地址
-> MASTER_PORT=3306, //主服务器端口
-> MASTER_USER='replication_user_name', //同步数据库的用户
-> MASTER_PASSWORD='replication_password', //同步数据库的密码
-> MASTER_LOG_FILE='recorded_log_file_name', //主服务器二进制日志的文件名(前面要求记住的参数)
-> MASTER_LOG_POS=recorded_log_position; //日志文件的开始位置(前面要求记住的参数)

6. Start the slave of the slave database

mysql> slave start;

The above is the detailed content of MySQL - Database dual-machine hot standby configuration method and code examples. For more information, please follow other related articles on the PHP Chinese website!