MySQL Master-slave replication

Get ready

Primary server ip:192.168.1.100

From server ip:192.168.1.101

Create the database on the primary server, add the initial data, and then import to the slave server

1. Install the MySQL service on the master/slave server respectively, the MySQL server version is best consistent, or the master server version is higher than the slave server

2. Create a user who synchronizes from the server on the primary service and set permissions

INSERT INTO Mysql.user (Host,user,password) VALUES (' localhost ', 'master ', Password (' 123456 '));

flush privileges;

Grant replication Slave on * * to ' master ' @ ' 192.168.1.101 ' identified by ' 123456 ' with GRANT option; /c1>

3. Set master-Slave synchronization

Primary Server

VI/ETC/MY.CNF #编辑配置文件, add the following in the [Mysqld] section

Server-id=1 #设置服务器id, 1 indicates the primary server, note: If the original configuration file already has this line, you can no longer add.

Log-bin=mysql-bin #启动MySQ二进制日志系统, note: If you already have this line in your original configuration file, you won't have to add it anymore.

Binlog-do-db=master-slave #需要同步的数据库名, if you have multiple databases, you can repeat this parameter, one row per database

Binlog-ignore-db=mysql #不同步mysql系统数据库

: wq! #保存退出

Service mysqld Restart #重启MySQL

From the server

VI/ETC/MY.CNF #编辑配置文件, add the following in the [Mysqld] section

server-id=2 #设置服务器id, 1 indicates the primary server, note: If the original configuration file already has this line, you can no longer add.

Log-bin=mysql-bin #启动MySQ二进制日志系统, note: If you already have this line in your original configuration file, you won't have to add it anymore.

Binlog-do-db=master-slave #需要同步的数据库名, if you have multiple databases, you can repeat this parameter, one row per database

Binlog-ignore-db=mysql #不同步mysql系统数据库

: wq! #保存退出

Service mysqld Restart #重启MySQL

Show master status; #查看主服务器, a message similar to the following appears

4. Set up sync from server and turn on sync to view sync status

Mysql>

Slave stop; #停止slave同步进程

    change Master to master_host= ' 192.168.1.100 ', master_user= ' mysql-bin.000011  ,master_log_pos=107 ;   

#执行同步语句 (the red parameter is executed on the primary server show master status; get the File, Position Parameters )

Slave start; #开启slave同步进程

Show SLAVE status\g #查看slave同步信息, the following appears to indicate that the synchronization is normal

Slave_io_running:yes

Slave_sql_running:yes

5. Test: Modify the data in the master server to see if the data changes from the server

This article is from the "program ape of those Years" blog, please be sure to keep this source http://uyuyuuy.blog.51cto.com/6190986/1791931

MySQL Master-slave replication