MySQL Master-slave replication

MySQL configuration for master-slave replication

First, the principle

The distribution of MySQL data across multiple systems is done by copying data from one of the MySQL hosts to the other host (slaves) and re-executing it again. One server acts as the primary server during replication, while one or more other servers act as slave servers. The primary server writes the update to the binary log file and maintains an index of the file to track the log loop. These logs can record updates that are sent to the slave server. When a primary server is connected from the server, it notifies the primary server where the last successful update was read from the server in the log. Receive any updates from the server from then on, and then block and wait for the primary server to notify the new updates.

Note: You can only operate on the primary server when updating data, or it will result in inconsistent data from the master and slave servers.

Second, the configuration of the primary server

1. Configuring the master configuration file

   #vim/etc/my.cnf

   [Server]
   

   Port = 3306

   Socket =/tmp/mysql.sock

   [Mysqld]

   user = MySQL

   Innodb_buffer_pool_size = 128M

   Log_bin = master-log \ \ Note Turn on the binary logging feature

   Max_binlog_size = 64M

   Binlog_format = mixed \\row (line), statement (statement, statement in the log that the action is visible)

   Basedir =/usr/local/mysql

   DataDir =/database/mydata

   Port = 3306

   server_id = 1 \\slave The ID on the host should be 2

   Socket =/tmp/mysql.sock

   Log_error =/database/mydata/server1.err

   Character_set_server = UTF8

   Explicit_defaults_for_timestamp = On

   Sql_mode=no_engine_substitution,strict_trans_tables

3. Database initialization

   #./scripts/mysql_install_db--user=mysql--datadir=/database/mydata

5. To authorize from the server

   mysql> grant replication Slave on * * to ' repuser ' @ ' 10.0.10.1 ' identified by ' 123123 ';

   mysql> flush Privileges;

   Mysql> Show Master Status\g

III. configuration from the server

2. Configuring the Master Profile

   #vim/etc/my.cnf

   [client]

   Port = 3306

   Socket =/tmp/mysql.sock

   [mysqld ]
   

   user = MySQL

   Innodb_buffer_pool_size = 128M

   max_binlog_size = 64M

   binlog_format = mixed

   Relay_log = relay-bin                \\ relay log

   Basedir =/usr/local/mysql

   datadir =/database/mydata

   Port = 3306

   server_id = 2

   Socket =/tmp/mysql.sock

   Log_error =/ Database/mydata/server1.err

   Character_set_server = UTF8

   Explicit_defaults_for_timestamp = on

   SQL _mode=no_engine_substitution,strict_trans_tables

4. Database initialization

   #./scripts/mysql_install_db--user=mysql--datadir=/database/mydata

6. Set up information about the server

   mysql> Change Master to master_host= ' 10.0.10.1 ', master_user= ' repuser ', master_password= ' 123123 ', master_log_file= ' master-log.000003 ', master_log_pos=409,master_port=3306;

   mysql> start slave;

   Mysql> Show Slave Status\g

   mysql> stop Slave;

Iv. related operations on binary logs

# Mysqlbinlog master-log.000001 \ \ View the contents of this log

# mysqlbinlog--start-datetime= ' 2015-07-13 7:10:0 ' master-log.000003

# mysqlbinlog--start-datetime= ' 2015-07-13 7:10:0 '--stop-datetime= ' 2015-07-13 7:33:0 ' master-log.000003

# Mysqlbinlog--start-position=330 master-log.000003

# Mysqlbinlog--start-position=330--stop-position=1100 master-log.000003

# mysqlbinlog--start-position=330--stop-position=1100 master-log.000003 | Mysql-u root \ Restore the contents of the specified binary log

Mysql> show master logs;       Mysql> show binary logs; \ \ View Log

Mysql> show Binlog events in ' master-log.000003 ' limit 20;

Mysql> show Binlog events in ' master-log.000003 ' from 409 limit 10;

Mysql> purge master logs to ' master-log.000002 '; \ \ Delete the log before the specified number

Mysql> purge master logs before ' 2015-07-22 08:00:00 ';

mysql> flush logs; \ \ Refresh Log

V. Other

Set the ignored database---only some specific data is copied

1. Primary server settings

binlog_do_db = zz \ \ Records the database of binary logs

binlog_ignore_db = test \ \ does not log binary log database

binlog_ignore_db = Teach

2. From the server settings

replicate_do_db = zz \ \ Sets the default database for binary log replication

replicate_ignore_db = test \ \ does not perform a binary log copy of the database

replicate_ignore_db = Teach

replicate_ignore_db = MySQL

replicate_ignore_db = Information_schema

replicate_do_table = zz.stu \ \ Set the table to be updated

replicate_ignore_table = zz.class \ \ does not update the table

3. Recommended settings from the server

replicate_ignore_db = test \ \ does not perform a binary log copy of the database

replicate_ignore_db = MySQL

replicate_ignore_db = Information_schema

replicate_wild_do_table = zz.stu \ \ Copy the binary log of the specified database or table

Replicate_wild_do_table = zz.%

replicate_wild_ignore_table = zz.class \ \ does not copy the binary log of the specified database or table

Another: Dual master replication---Update operation can be performed on any one host

First server settings

auto_increment_increment = 2 \ \ Data table records the self-increment, which is generally equal to the number of servers

Auto_increment_offset = 1 \ \ data table records each increment, the first is 1, the second is 2,...

Sync_binlog = 0 \ \ Binary log how to write to disk (synchronous 1 or unsynchronized 0)

replicate_same_server_id = 0 \ \ Prevent MySQL loop update

mysql> grant replication Slave on * * to ' repuser ' @ ' 10.0.10.1 ' identified by ' 123123 ';

mysql> flush Privileges;

Second server settings

mysql> Change Master to master_host= ' 10.0.10.1 ', master_user= ' repuser ', master_password= ' 123123 ', master_auto_ Position=1;

mysql> start slave;

This article is from "Rookie in Growth" blog, please be sure to keep this source http://shuaiz.blog.51cto.com/10626377/1697502

MySQL Master-slave replication