MySQL Master-slave Replication (Replication) (MySQL data synchronization) configuration

MySQL is an open-source relational database system. Replication (Replication) is a process that replicates data from one MySQL database server (master server master) to another server (from server slave).

Configuring the primary server (master)

1, edit the database configuration file my.cnf, generally in the/etc/directory.

1. #vi/etc/my.cnf

Add the following code below [mysqld]:

1. Log-bin=mysql-bin
2. Server-id=1
3. Innodb_flush_log_at_trx_commit=1
4. Sync_binlog=1
5. Binlog-do-db=wordpress
6. Binlog_ignore_db=mysql

The 1 in server-id=1 can be defined as long as it is unique.
Binlog-do-db=wordpress is to represent only the backup WordPress.
Binlog_ignore_db=mysql indicates that backup MySQL is ignored.
Without binlog-do-db and binlog_ignore_db, that means backing up all the databases.
2. Then restart MySQL:

1. #service mysqld Restart

3. Log in to the MySQL server.

1. #mysql-uroot-p

Create a new user grant "REPLICATION SLAVE" permission on the primary server. You don't need to give other permissions. In the following command, replace the x.x.x.x with the IP from the server.

1. Mysql>create user ' user ' @ ' x.x.x.x ' identified by ' password ';
2. Mysql>grant REPLICATION SLAVE on * * to ' user ' @ ' x.x.x.x ' identified by ' password ';

4. Execute the following command to lock the database to prevent writing data.

1. Mysql>flush TABLES with READ LOCK;

Do not exit the MySQL command line at this point, because the global table lock is invalidated after exiting the command line, and a new window continues to execute the following command.
5. Export the database

1. #mysqldump-u root-p123456--all-databases >/root/all.sql

6. Connect the database again to the MySQL command line to view the master status.

1. Mysql>show MASTER STATUS;

Make a note of the information that is displayed, which is used by the configuration from the server.
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 1001741 | Dbispconfig | MySQL |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)
7. Unlock the data sheet.

1. Mysql>unlock TABLES;

8. Use the SCP command to transfer the database file All.sql to the slave server.

1. #scp/root/all.sql [Email protected]:/root

Configuring the Slave server (slave)

Log on from the server.
1. Import the database of the master server.

1. #mysql-U root-p123456 </root/all.sql

2, edit the configuration file my.cnf, add under [mysqld]:

1. server-id=2

2 can be defined by itself, as long as the only guarantee is unique.
3. Save the file and restart Mysqld.

1. #service mysqld Restart

4. Log in to the MySQL server and execute the following command.

1. Mysql>change MASTER to
2. Master_host= ' x.x.x.x ',
3. Master_user= ' USER ',
4. Master_password= ' PASSWORD ',
5. master_port=3306,
6. Master_log_file= ' mysql-bin.000001 ',
7. Master_log_pos=98,
8. master_connect_retry=10;

Master_host: The IP of the primary server.
Master_user: User name established when configuring the primary server
Master_password: User Password
Master_port: The primary server MySQL port, if not modified, by default.
5, start the slave process.

1. Mysql>start SLAVE;

6, check the MySQL log, generally in the/var/log/directory, if the startup is successful, you should see a log similar to the following.
091104 8:42:02 [Note] Slave I/O thread:connected to master ' [e-mail protected]:3306?, replication started in log ' Mysql-bi N.000001? At position 98
Now the master server and the slave server are all configured. In addition, you may need to set the expiration time of the primary server's database binary log, which can be set using the parameter Expire_logs_days in the configuration file.

Check for normal operation

1. Perform the show Master STATUS on the primary server, and position should be nonzero if it is already logged. If there are no records, confirm that the primary server is running with the Log-bin and Server-id options.
2. Perform showshow SLAVE STATUS from the server to check if slave_io_running and slave_sql_running values are yes. If not, verify the option to use when starting from the server.
3. If the server is running, is the connection with the primary server established? Use show processlist to find I/O and SQL threads and examine their state columns to see how they are displayed. If the I/O thread status is connecting to master, verify the permissions of the replication user on the primary server, the primary server hostname, the DNS settings, whether the primary server is actually running, and whether it can be accessed from the secondary server.

Issues to be aware of

1, the primary server and the time zone from the server must be the same, or MySQL to perform the temporal-related functions will result in inconsistent data.

MySQL Master-slave Replication (Replication) (MySQL data synchronization) configuration