Mysql database master-slave synchronization configuration method

This article describes how to back up the master-slave configuration of the mysql database, how to back up the slave command for the master-slave configuration of mysql, and how to skip temporary errors during Database Synchronization. For more information, see.

This article describes how to back up the master-slave configuration of the mysql database, how to back up the slave command for the master-slave configuration of mysql, and how to skip temporary errors during Database Synchronization. For more information, see.

After installing mysql

Open the primary database my. cnf

Mkdir/home/log/

Add

Configuration code

The Code is as follows:
01. ########## start of synchronization configuration ################### 02. server-id = 1 03. log-bin =/home/log/mysql_serverid.log 04. set-variable = binlog-ignore-db = mysql 05. set-variable = binlog-ignore-db = test 06. ########## synchronization configuration ends ###################

Open my. cnf from the database

Configuration code

The Code is as follows:

01. ## mysql synchronization ### 02. server-id = 2 (Note: You must set different unique IDs for different slave databases) 03. master-host = 192.168.0.1 04. master-user = bak 05. master-password = password 06. master-port = 3306 07. master-connect-retry = 10 08. set-variable = replicate-ignore-db = mysql 09. set-variable = replicate-ignore-db = test 10. lower_case_table_names = 1 11. ## mysql synchronization ###

Yes...

Mysql slave command backup

The Code is as follows:
Mysql> slave stop; Mysql> change master to master_host = '192. 168.5.188 ', master_user = 'slave-user', master_password = '000000', master_log_file = 'mysql-bin.000063', master_log_pos = 98; Mysql> start slave;

Temporary error skipping during mysql Database Synchronization

The Code is as follows:
Slave stop; Set GLOBAL SQL _SLAVE_SKIP_COUNTER = 1; Slave start;

Several mysql commands related to Hot Standby: (the mysql command line interface or query is required)

Stop slave # stop Synchronization
Start slave # start synchronization and update from the log termination location.
SET SQL _LOG_BIN = 0 | 1 # run on the host. The super permission is required to enable or stop the log. If the log is enabled or disabled at will, the data on the host slave will be inconsistent, resulting in errors.
Set global SQL _SLAVE_SKIP_COUNTER = n # Run the client to skip several events. It can be executed only when the synchronization process stops when an error occurs.
Reset master # run on the host to clear all logs. This command is the original flush master.
Reset slave # Run from the machine, clear the log synchronization location mark, and regenerate master.info
Although master.info is re-generated, it cannot be used. It is best to restart the mysql process on the slave machine,
Load table tblname from master # run on the slave machine. The data in the specified TABLE can be re-viewed FROM the host. Only one TABLE can be read at a time. Due to the time limit of timeout, you need to adjust the timeout time. To execute this command, the synchronization account must have the reload and super permissions. And have the select permission on the corresponding database. If the table is large, add the net_read_timeout and net_write_timeout values.
Load data from master # Run FROM the slave machine and read all the data from the host. To execute this command, the synchronization account must have the reload and super permissions. And have the select permission on the corresponding database. If the table is large, add the net_read_timeout and net_write_timeout values.

The Code is as follows:

Change master to master_def_list # online CHANGE of some host settings. Separate multiple settings with commas (,). For example CHANGE MASTER MASTER_HOST = 'master2 .mycompany.com ', MASTER_USER = 'replicase ', MASTER_PASSWORD = 'bigs3cret' MASTER_POS_WAIT () # Run from the slave machine Show master status # Run the host and view the log export information Show slave hosts # Run the host to check the connected SLAVE machine. Show slave status (slave) Show master logs (master) Show binlog events [IN 'logname'] [FROM pos] [LIMIT [offset,] rows] PURGE [MASTER] logs to 'logname'; PURGE [MASTER] logs before 'date'