Linux 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 a MySQL database server (master server master) to another server (slave from the server).

Configure Primary server (Master)
1, edit the database configuration file my.cnf, generally in the/etc/directory.

1. #vi/etc/my.cnf
Under [Mysqld], add the following code:

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
1 of the server-id=1 can be defined, as long as the only one is on the line.
Binlog-do-db=wordpress is to indicate that only WordPress is backed up.
Binlog_ignore_db=mysql indicates that the backup MySQL is ignored.
Without binlog-do-db and binlog_ignore_db, that means backing up all the databases.
2, and then restart MySQL:

1. #service mysqld Restart
3, login MySQL server.

1. #mysql-uroot-p
Create a new user on the primary server that gives "REPLICATION SLAVE" permission. You don't have 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 time, because the global table lock fails after exiting the command line, and a new window continues to execute the following command.
5. Export Database

1. #mysqldump-u root-p123456--all-databases >/root/all.sql
6, use the SCP command to transfer the database file All.sql to the server.

1. #scp/root/all.sql Root@www.example.com:/root
7, connect the database again into the MySQL command line to view master status.

1.mysql>show MASTER STATUS;
Note 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)
8, unlock the data table.

1.mysql>unlock TABLES;
Configure from server (slave)
Log in 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 the only line.
3, save the file and restart Mysqld.

1. #service mysqld Restart
4, login to the MySQL server, 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 the master server is configured
Master_password: User Password
Master_port: master server MySQL port, if not modified, default.
5, start the slave process.

1.mysql>start SLAVE;
6, view the MySQL log, generally in the/var/log/directory, if the startup success, you should see similar to the following log.
091104 8:42:02 [note] Slave I/O thread:connected to Master ' root@x.x.x.x:3306. Replication started in log ' mysql-bin.000 001? At position 98
The primary server and the server are now 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 show Master STATUS on the primary server, and position should be non-zero if it has been recorded. If there are no records, confirm that you are running the primary server with the Log-bin and Server-id options.
2. Perform showshow SLAVE STATUS from the server to check whether slave_io_running and slave_sql_running values are yes. If not, verify the option that is used when starting from the server.
3. If you are running from the server, have you established a connection to the primary server? Use show processlist to find I/O and SQL threads and check their state columns to see how they are displayed. If the I/O thread state is connecting to master, verify the permissions of the replicated user on the primary server, the host name of the primary server, 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 consistent, otherwise MySQL execution time related functions will result in inconsistent data