MySQL configuration master-slave backup

MySQL master-slave backup Configuration instance

Scene:

1, the primary server 192.168.0.225, from the server 192.168.0.226. Where the data is already available on the master server.

2, the MySQL version on the master and slave server and the same installation configuration.

First, the principle of master-slave backup:

Each operation of the primary server database is recorded in the binary log file mysql-bin.xxx. The I/O thread from the server uses a private account to log on to the primary server to read the binary, and writes the contents of the file to its local trunk log relay-log file. The SQL thread from the server then executes the SQL statement based on the contents of the trunk log.

This requires that both servers have the same initial state.

Second, synchronous initial state:

1, the main server to synchronize the database lock, to avoid the synchronization changes:

> Usedatabase_name; >flush tables with read lock;

2. Use the Mysqldump tool to export data:

MYSQLDUMP-UROOT-PXXX database_name >database_name.sql

3. After the backup is complete, unlock the database:

>unlock tables;

4. Import the initial data from the database:

>CREATE DATABASE database_name; > Usedatabase_name; >source Database_name.sql;

After doing this, the master and slave servers have the same initial state.

Third, master-slave synchronization settings:

1. Configuration from the database:

/ETC/MY.CNF main configuration is as follows:

Log-bin=mysql-bin                                 # Open binary log     server-id       = 2                               # The primary database ID is 1 and cannot be the same. replicate_wild_do_table=test.%                    # synchronize only the table under the test library Relay_log=mysqld-relay-bin                        # Record trunk log log-slave-updates=yes                             # log after synchronizing from server

Restart the MySQL service after the modification is complete.

2. View the primary server journal record location:

>show Master Status\g

The contents of the display are as follows:

1. Row ****************            file:mysql-bin.000001       # Log currently logged        position:80647293                # location    recorded in the log in Set (0.00 sec) 

3. The primary server creates an account that allows data to be synchronized from the server:

' user_name ' @'192.168.0.226'ahaii';

4. Start the synchronization from the server:

> ChangeMaster to                   master_host='192.168.0.225',                             master_user ='user_name',                  master_password='ahaii ' ,                 master_log_file='mysql-bin.000001',                  master_log_ pos= 80647293;

After configuring the above, restart the MySQL service from the server.

5. To see if the synchronization has been successfully turned on from the server:

>show slave Status\g

Shown below:

1. Row ***************************slave_io_state:waiting forMaster to send event Master_host:192.168.0.225master_user:user_name Master_port:3306Connect_retry:60Master_log_file:mysql-bin.000001Read_master_log_pos:1114Relay_log_file:mysqld-relay-bin.000004Relay_log_pos:1260Relay_master_log_file:mysql-bin.000002Slave_io_running:yes Slave_sql_running:yes replicate_do_db:replicate _ignore_db:replicate_do_table:replicate_ignore_table:replicate_wild_do_table:test.%replicate_wild_ignore_table:last_errno:0 Last_error:skip_co unter:0 Exec_master_log_pos:1114Relay_log_space:1563Until_condition:none until_log_file:until_log_pos:0 MASTER_SS L_allowed:no Master_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Mast                Er_ssl_cipher:master_ssl_key:seconds_behind_master:0master_ssl_verify_server_cert:no last_io_errno:0 last_io_error:last_sql_errno:0 Last_sql_error:repli CATE_IGNORE_SERVER_IDS:MASTER_SERVER_ID:RowinchSet (0.00 sec)

Where: The status of Slave_io_running and slave_sql_running is yes, indicating that the synchronization is successful.

You can now go to the test library on the master server to create the table to open the testing synchronization.

MySQL configuration master-slave backup