8.31 MySQL master-slave configuration, preparation, configuration master, configuration slave, test master-Slave synchronization

1. mysql Master-slave configuration

MySQL master-slave is also called replication, AB replication. Simply speaking, A and b two machines from the back, write the data on a, the other B will follow the writing data, the two data is real-time synchronization

MySQL master-slave is based on Binlog, the Lord must open Binlog to carry out master and slave.

The master-slave process has a roughly 3-step

1) The change operation is recorded in Binlog.

2) from synchronizing the main binlog event (SQL statement) to the machine and recording it in Relaylog

3) Execute sequentially from the SQL statements inside the Relaylog

The Lord has a log dump thread that is used to communicate with the I/O thread from Binlog

There are two threads from the top, where I/O threads are used to synchronize the main binlog and generate Relaylog, and another SQL thread is used to put the SQL statements inside the Relaylog

2. Configure the main

　Lord Operations

Install MySQL

Modify MY.CNF, add server-id=130 and Log_bin=aminglinux1

After modifying the configuration file, start or restart the mysqld service,/etc/init.d/mysql Restart, after the restart to view/data/mysql directory, found to produce a number of aminglinux1-prefixed files, this file is to implement master-slave main file

Backup and restore MySQL library to aming library as test data

Mysqldump-uroot mysql >/tmp/mysql.sql

MYSQL-UROOT-E "CREATE Database Aming" creates a new library

Mysql-uroot aming </tmp/mysql.sql, restore the backup file to the newly created library

Create a user to use to synchronize data

Enter the database and execute the following command:

Grant Replication Slave on * * to ' repl ' @slave_ip identified by 'password';

Flush tables with read lock, lock table, stop data write

Show master status;

3, configuration from　

Action from top

Install MySQL

View my.cnf, configuring server-id=132, requirements not the same as the master

Never need to add log_bin parameter, after modifying the configuration file, start or restart the Mysqld service

Sync the Lord Aming library to the top

You can create the Aming library first, then copy the/tmp/mysql.sql of the Lord to the top, and then import the Aming library

After creating the table, perform the following command to recover:

Mysql-uroot Blog </tmo/blog.sql

Mysql-uroot Zrlog </tmo/zrlog.sql

Mysql-uroot Aming </tmo/aming.sql

Then execute the following command to implement the master-slave

Mysql-uroot

Stop slave;

Change Master to master_host= ', 192.168.1.130 master_user= ' repl ', master_password= ', master_log_file= ' Aminglinux1.000001 ', master_log_pos=474566;///This command is an important configuration for implementing master-Slave

Start slave;

Then perform a show slave status\g view from the status, here should be shown as two Yes

And go to the Lord to execute unlock tables

4. Test Master-Slave synchronization

On the primary server

binlog-do-db=//Synchronize only the specified libraries

binlog-ignore-db=//Ignore specified library

From the server

replicate_do_db=

replicate_ignore_db=//Not used

replicate_do_table=//sync only which tables, not commonly used

replicate_ignore_table=//Ignore which tables, this operation is easy to lead to incomplete data, so we recommend the following two kinds of actions

replicate_wild_do_table=//As aming.%, wildcard% supported

replicate_wild_ignore_table=

Master-Slave testing

Our Lord

Mysql-uroot aming

Select COUNT (*) from DB;

TRUNCATE TABLE DB, at which time the db is empty

To from the top

Mysql-uroot aming

Select COUNT (*) from DB;

The Lord continues to drop table db;

View the DB table from above, there is no table at this time

8.31 mysql master-slave configuration, preparation, configuration master, configuration slave, test master-Slave synchronization