MySQL master-Slave synchronization (replication) configuration

1, the principle of master-slave replication:

*master, record data change operations

- Enable Binlog logging mode

- allow Slave to read binlog logs

*slave running 2 simultaneous threads

-Slave_io: Responsible for connecting Master, copy its binlog log file to the Relay-log file of the machine

-Slave_sql: Executes the SQL statement in the native Relay-log file to reproduce the Master 's data operations

2, basic construction ideas:

1) Initialize the existing library: Import the main library from the library to ensure consistency of data

2) Configure Master, master server: Adjust the operating parameters, authorize a synchronous user

3) Configure Slave, from server: Adjust running parameters, initiate synchronous operation

3. Initialize the existing library:

* * you must have libraries and tables for the primary database server from the database server, but you do not need to have libraries and tables from the database server on the primary database server. From the server should include all the libraries you want to synchronize, for libraries with MyISAM , you can back up offline.

Mysql>reset master; reset binlog log

[[Email protected]~] #mysqldump-uroot-p123-b mysql test db6000 >/dbbak/threedb.sql// full backup Primary Server database file

For slave servers, import the backup provided by the primary server offline, emptying the library with the same name (if any)

Mysql>drop database test;

[[Email protected]~] #scp svr:/dbbak/threedb.sql/root/// remote Copy Home server backup file

[[Email protected]~] #mysql-uroot-p123 <threedb.sql// Recover the backup file of the primary server to the native database

* * To modify the value of the following file UUID from the database server if it is a cloned virtual machine

[email protected] mysql]# cat /var/lib/mysql/auto.cnf

[Auto]

Server-uuid=a1c22f29-7d1a-11e4-b7f9-099e0022adfd

4. Configure the primary server:

1) Adjust operating parameters, enable Binlog and allow synchronization

[Email protected]~] #vim/etc/my.cnf

[Mysqld]

Log_bin// enable binlog log

server_id=6// Specify the server ID number, identify yourself, and generally use the IP address host bit

Innodb_flush_log_at_trx_commit=1// optimization settings

Sync-binlog=1// Allow log synchronization

......

[[email protected]~] #service mysql restart//Restart service to make the settings effective

2) Authorized Backup User: Allow slaveuser to access from the 192.168.4.0/24 network segment and synchronize permissions on all libraries (not allowed for a single library by default)

mysql>Grant replication Slave on * * to [email protected] ' 192.168.4.% ' identified by ' 123 ';

3) Check the status of the master server, remember the current binlog log file name, offset POS point number

Mysql>Show Master status\g; //View the Binlog log file name and offset POS point number that is currently in use

file:svr6-bin.000003// log file name

position:335// offset

......

5. Configure from server:

1) Adjust the operating parameters, enable Binlog and allow synchronization, enable read-only mode

[Email protected]~] #vim/etc/my.cnf

[Mysqld]

Log_bin// enable binlog log

SERVER_ID=20// Specify server ID number

Innodb_flush_log_at_trx_commit=1// optimization settings

Sync-binlog=1// Allow log synchronization

Read_only=1// read-only mode

......

[[email protected]~] #service mysql restart//Restart service to make the settings effective

2) initiate the synchronization operation, specify the parameters of the main server

[Email protected]~] #mysql-uroot-p123

Mysql> Show slave status;

mysql> Changemaster to master_host= "192.168.4.6",

Master_user= "Slaveuser",

Master_password= "123456",

master_log_file= "svr6-bin.000003", //binlog log file

master_log_pos=335; offset POS point number

mysql> showslave status\g;

Slave_io_running:no

Slave_sql_running:no

mysql>start slave; Start Replication

Mysql> show Slave status\g; view from server status

Slave_io_running:yes//io thread is running

Slave_sql_running:yes//sql thread is running

6, test master-Slave synchronization effect:

In the primary database server native login, build the database to build a table, from the databases server on the primary database server on the library and tables, the primary database server to perform the INSERT, update operation, from the server has synchronized updates, the success.

7 ,MySQL how database Master-Slave synchronization works :

1)slave_io_running: is responsible for the main database server is currently in use in the binlog log SQL statements, copied to the native Relaybinlog In the log, copy from Master_log_pos in the master_log_file file.

Master.info : Save master-Slave related information (change master to ...) ) to load when the service is started.

pc205-relay-bin.000002 Pc205-relay-bin.index Relay-log.info

Vim/etc/my.cnf

[Mysqld]

Relay_log= ...

2)slave_sql_running: Responsible for executing the SQL statements in the native Relaybinlog log , and writing the data into the database

3) error message:

IO Process Error: last_io_errno:0// error count when viewing status

Last_io_error:// last error message

IO error Condition:

- connection is not on the primary database server (ping iptables selinux authorized Primary server currently using the log file and node is wrong (use show Master status on the primary server) View the use change Master to ... from the server. Reset, to stop process stop slave; change after start slave;) )

SQL Process error:last_sql_errno:0

Last_sql_error:

SQL Error: Inconsistent with the library, table, and table structure on the primary database server (Restore the library, table backup on the primary server to the slave server)

4) Stop from the server:

Stop slave;

Delete Master.info pc205-relay-bin.000002 Pc205-relay-bin.index Relay-log.info

8 , master-slave synchronous structure mode :

1) One Master one from: When the primary server is broken access from the server, the data from the server is not backed up, and the data from the primary server is not backed up when the server is broken

2) a master multi-slave: When configured on the primary server to authorize multiple slave users to connect themselves on the host from the server, or to authorize a user at @ "%" all hosts to connect themselves (unsafe).

3) Master slave from: Configure master-Slave, configure from from (with master-slave), primary server and from the server out of sync, client connections from the server generated SQL statements recorded to the binlog log itself, while the copy of the primary server SQL The statement is logged in the Relaybinlog log, from the server itself only Binlog logs from the server . To synchronize from the primary server and from the server, to configure the slave server:

Vim/etc/my.cnf

[Mysqld]

......

log-slave-updates // records are updated from the library, allowing chained copy (a-b-c) configuration to be synchronized from the primary server to the log that is written to Relaylog also writes to its own Binlog log so that it can read the log from the server according to Binlog to synchronize the data

......

4) Main structure: mutual main from

9. Only one or some of the libraries of the primary server need to be backed up from the server:

Authorization cannot be granted only for a library,replicationslave authorization must be granted globally

* * when configuring master-slave synchronization, set up on the primary database server how to back up your library from the database server:

Vim/etc/my.cnf

[Mysqld]

......

binlog-do-db= database name // set which libraries the primary server logs Binlog log, this option can set more than one, copy all libraries when omitted

binlog-ignore-db= database name // set which libraries the primary server does not remember Binlog logs (two contradictions, not simultaneous)

......

* * or when configuring master-slave synchronization, set your own library on how to back up the primary database server from the database server:

Vim/etc/my.cnf

[Mysqld]

......

replicate-do-db= database name // Copy only the specified library, other libraries will be ignored, this option can be set multiple, copy all libraries when omitted

replicate-ignore-db= data name // do not copy which libraries (two contradictions, not concurrent use)

* * set only on the primary server or from one end of the server.

This article is from the IT Technology Learning blog, so be sure to keep this source http://learningit.blog.51cto.com/9666723/1789591

MySQL master-Slave synchronization (replication) configuration