"MySQL" MySQL master-slave copy

MySQL master-slave replication configuration

Primary server: 192.168.0.100

From the server

192.168.0.101

Primary server Configuration

My.ini (Window under Linux is MY.CNF)

#开启二进制日志

Log-bin=mysql-bin

#给服务器起一个唯一的id

Server-id=1

#指定日志格式

Binlog-format=mixd

Authorization to 192.168.0.101 from the database server
Mysql> GRANT REPLICATION SLAVE on * * to ' user ' @ ' 192.168.0.101 ' identified by ' password ';

Querying the primary database state
Mysql> Show master status;

+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 | 998 | | |
+------------------+----------+--------------+------------------+

From server configuration

#配置从服务器
Log_bin=mysql_bin
server_id=2
Relay_log=mysql-relay-bin
Log_slave_updates=1
Read_only=1

Executing a synchronous SQL statement
Mysql> Change Master to
Master_host= ' 192.168.0.100 ',
master_user= ' user ',
master_password= ' Password ',
Master_log_file= ' mysql-bin.000008 ', #和上面的一致
master_log_pos=261;

Start slave synchronization process after correct execution
mysql> start slave;

Master-Slave synchronization check
Mysql> Show Slave Status\g

When MySQL master-slave replication occurs in show slave status\g when the value of slave_io_running or slave_sql_running is not yes, you need to stop the slave server first through stop slave, Then perform the above steps again to recover, but if you want to synchronize more data as possible, you can slave the value of the Master_log_pos node on the basis of the value of the previous synchronization failure, and then test repeatedly until the synchronization OK. Because the principle of MySQL master-slave replication is to read the master server from the server Binlog, and then update the database according to the Binlog Records