Construction of mysql master-slave replication environment

Mysql master and slave

The first time I do it with different versions of mysql, there will be an out-of-sync problem. It is recommended to use the same version of mysql as the master and slave.

Main my.cnf file (192.168.1.64)

Mysqld module joining

Log-bin=mysql-bin boot binaries

Server-id=1 Server ID

Binlog-do-db = the library name that needs to be copied can be used, split

If there is data in the master database

First of all, the table locking operation is carried out to prevent the data from being written, which is done in order to prevent the original data from the slave database from being inconsistent with the original data from the master database.

Master enters mysql

Mysql > flush tables with read lock

Mysqldump-uroot-p123456 testDB > / home/testDB.sql

Scp-r / home/testDB.sql root@192.168.1.12:/home

From entering mysql

Create database testDB

Mysql-u root "your database name"

< "你的sql文件" 做完之后进行unlock tables; 解锁表操作 进入mysql新建用户并且给从使用第一步grant replication slave on *.* to 'test'@'192.168.1.12' identified by '1234';第二步Grant all on testDB.* to 'test'@'192.168.1.12' identified by '1234' with grant option;show master status;| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000002 | 106 | testDB | |+------------------+----------+--------------+------------------+ 2.从my.cnf文件（192.168.1.12） mysqld模块加入 log-bin=mysql-bin 启动二进制文件 server-id=2 服务器ID 进入mysqlchange master to master_host='192.168.1.64',master_user='test',master_password='1234',master_log_file='mysql-bin.000002',master_log_pos=2852;start slave;show slave status\Gmysql>

Show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.64 Master_User: test Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 2852 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 251 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes

Slave_IO_Running and SQL are required to be equal to yes

If one of them doesn't have yes,

Check database user permissions and firewalls

Log in to the slave server and test mysql-h292.168.1.64-utest-p

Checking serverip

Mysql > show variables like 'server_id'

Or slave stop; slave start.

If it doesn't work, install two identical versions of mysql to test it.