Mysqlreplication official Chinese document _ MySQL

Mysqlreplication official Chinese document bitsCN.com

Mysql replication official Chinese document

Preparations:

1. ensure that the mysql versions on the Master and Slave hosts are consistent to avoid unnecessary troubles.

2. ensure that the Master/Slave can ping each other and communicate with each other.

Master host IP address: 192.168.1.1

Slave host IP address: 192.168.1.2

Configure my. ini:

Master-my. ini

Add the following under [mysqld:

Log-bin = mysql-bin # because MYSQL is synchronized based on binary logs, the size of each log file is 1 GB.

Server-id = 1 # id of the primary service, which must be unique

Binlog-do-db = db_user # Name of the database to be synchronized

Binlog-do-db = db_topic

Binlog-ignore-db = mysql, test # databases that do not record logs, that is, databases that do not need to be synchronized

Slave-my. ini

Add the following under [mysqld:

Server-id = 2

Log-bin = mysql-bin # if bidirectional synchronization is not performed, the slave machine does not need to write binary logs.

Log-slave-updates

Master-host = 192.168.1.1

Master-user = slave # user name for the server load balancer host to access the Master

Master-password = 123456 # password used by the Slave host to access the Master

Master-port = 3306

Replicate-ignore-db = mysql # databases that are not synchronized

Replicate-do-db = db_user # database to be synchronized

Replicate-do-db = db_topic

Restart Master and Slave

# Net stop mysql

# Net start mysql

Mysql> show master status; # check whether my. ini is configured successfully

Create an account (Slave access Master)

Mysql> grant replication slave, reload, super, select on *. * to slave @ '%' identified by '000000 ′;

Mysql> flush privileges;

Back up the database of the Master host (db_user, db_topic)

# If the table structure is MyISAM, you can copy the file directly.

Mysql> flush tables with read lock; # lock and prohibit write operations

Mysql> mysqldump-uroot-proot-P3306-databases db_user db_topic> backdb. SQL

Import Master data to Slave

If the structure of all database tables is MyISAM

Mysql> load data from master;

Otherwise

Mysql-uroot-proot <backdb. SQL

Stop Slave

Mysql> stop slave;

Tell slave the current master value

Mysql> change master to master_host = '2017. 168.1.1 ', master_user = 'Slave', master_password = '000000', master_port = 123456, master_log_file = 'MySQL-bin.000001 ', master_log_pos = 98;

Mysql> start slave; # start synchronization

Check Slave status

Mysql> show slave status/G;

# When the three most important fields are the following values, the operation is successful.

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

Seconds_Behind_Master: 0

Back to Master

Mysql> unlock tables; # unlock

At this point, the Master/Slave should be configured,

You can try to add a record to db_user of the Master, and select

BitsCN.com