MySQL Master-slave replication

MySQL's master-slave replication
In the actual enterprise application environment, the single MySQL database is insufficient to meet the future business requirements. For example, if a server fails and there is no backup server to provide the service, the business will have to stop. In this case, let's learn MySQL master-slave replication.
The benefits of using MySQL master-slave replication are:
1, the use of master-slave server This architecture, stability can be improved. If the primary server fails, we can use the slave server to provide the service.
2, processing the user's request separately on the master-slave server, can improve the data processing efficiency.
3. Copy the data on the primary server to the slave server to protect the data from accidental loss.

Environment
I'm using Percona 5.5.
Host ip:192.168.1.110
Slave machine ip:192.168.1.111
I. MySQL master server configuration
1. Edit the configuration file/etc/my.cnf
server_id = 1
Log-bin=mysql-bin
#只输入以上两行表示实例下的数据库都进行复制
Binlog-do-db=mysql #需要备份的数据库名, if you back up multiple databases, set this option repeatedly
Binlog-ignore-db=mysql #不需要备份的数据库名, if you back up multiple databases, set this option repeatedly
Log-slave-updates #这个参数一定要加上, otherwise the updated records will not be sent to the binary file.
Slave-skip-errors #是跳过错误, continue with the copy operation #注意我使用的版本添加此项后启动mysql失败

2. Create a user
mysql> grant replication Slave on * * to [e-mail protected] identified by ' 123456′;
3. Lock Main Library table
Mysql> FLUSH TABLES with READ LOCK;
#在线上服务器, because the user data is constantly being written, if the table is not locked, it may be out of sync with the master-slave database
4. Display the main library information
Record file and position, from library settings will be used
=====================
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |              106 |                  | |
+------------------+----------+--------------+------------------+
5. Back up the database,


Two, MySQL from server configuration
1, restore the data this step is omitted
2, edit/etc/my.cnf
server-id=2
Log-bin=mysql-bin
After Setup, it is best to restart MySQL, otherwise it will appear to read server_id 1, as synchronization error
3, set the synchronization on the slave
mysql> slave stop;
mysql> change MASTER to master_host= ' 192.168.1.110 ', master_user= ' slave ', master_password= ' 123456 ', Master_log_ File= ' mysql-bin.000001 ', master_log_pos=106;
6. Start slave Service
mysql> slave start;

7. View slave status
mysql> SHOW SLAVE status\g;
Where the values of the slave_io_running and slave_sql_running two columns are "Yes", indicating that Slave I/O and SQL threads
are running normally.
8. Unlock the main library table
Mysql> UNLOCK TABLES;
To this master-slave library to build success. You can insert data on the main library to test whether synchronization is normal.



Iii. Common Errors and solutions:
1: Show slave status\g from the top of the library;
Slave_io_running:yes
Slave_sql_running:no
Seconds_behind_master:null

Reason:
A. The program may have been written on slave
B. It may also be the result of a transaction rollback after the slave machine is reset.

Workaround:

Enter Master

Mysql> Show master status;
+----------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+----------------------+----------+--------------+------------------+
| mysql-bin.000040 | 324 | | |
+----------------------+----------+--------------+------------------+
Then perform a manual synchronization on the slave server

Slave stop;
Change Master to
Master_host= ' 192.168.1.110 ',
Master_user= ' slave ',
Master_password= ' 123456 ',
master_port=3306,
Master_log_file= ' mysql-bin.000040 ',
master_log_pos=324;
Slave start;
show slave status\g;

2, the phenomenon: from the database can not sync, show slave status display slave_io_running for No,seconds_behind_master
Is null

Workaround: Restarting the primary database requires a different Server-id
View Server-id
Mysql> Show variables like ' server_id ';
Manually modifying Server-id
mysql> set global server_id=2; #此处的数值和my. CNF, just set it up.
mysql> slave start;

#service MySQL Restart

Mysql> Show master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 98 | | |
+------------------+----------+--------------+------------------+
Slave stop;
Change Master to master_log_file= ' mysql-bin.000001 ', master_log_pos=98
Slave start;
or this:
Stop slave;
Set global sql_slave_skip_counter = 1;
Start slave;

This phenomenon is mainly the master database problems, I in the actual operation restart the master after restarting slave can solve the problem,
This problem occurs when you have to restart the master database.

1. Primary and Secondary library synchronization is mainly through the binary log to achieve synchronization.
2. When you start the secondary library, you must synchronize the data and delete the: Master.info file in the log directory. Because Master.info Records
The last information to connect to the main library, if not deleted, even if the MY.CNF has been modified, does not work. Because the reading is still
The information in the Master.info file.

In the MySQL replication environment, there are 8 parameters that allow us to control the need to copy or to ignore the DB or table that does not replicate, respectively:
The following two items need to be set on master:
BINLOG_DO_DB: Set which databases need to be logged Binlog
BINLOG_IGNORE_DB: Set where the database does not need to be logged Binlog

The advantage is that the Binlog record on the master side reduces the amount of IO, the network IO decreases, and the IO thread on the slave side, the SQL thread is reduced,
To dramatically improve replication performance,
The disadvantage is that MySQL determines whether an event needs to be replicated rather than the DB of the query that generated the event, depending on where the query was executed
Default database (that is, the library name specified at login or the db specified in "Use database"), only the current default DB and Configuration
The IO thread will not read the event to the IO thread of the slave when the specified db is fully matched. So, if the default DB and settings need to be copied
DB changes the data in a table in the DB that needs to be copied, the event is not copied into the slave, so
Will cause data in the slave end to be inconsistent with the master data. Similarly, changes to the data in the database that do not need to be replicated are made under the default database.
will be copied to the slave end, and when the slave side does not have the database, it will cause replication errors and stop.

The following six items need to be set on slave:
REPLICATE_DO_DB: Sets the database to be replicated, with multiple DB separated by commas
REPLICATE_IGNORE_DB: Sets the database that can be ignored.
Replicate_do_table: Sets the Table to be copied
Replicate_ignore_table: Setting a Table that can be ignored
Replicate_wild_do_table: Functions with replicate_do_table, but can be set with wildcard characters.
Replicate_wild_ignore_table: function with replicate_do_table, function with replicate_ignore_table, can carry wildcard characters.

The advantage is to set the replication filtering mechanism on the slave side, which guarantees that the slave and master data will not occur because of the default database problems.
An issue with inconsistencies or replication errors.
The disadvantage is that the performance is worse than the master end. The reason is that the IO thread reads the event to the slave port, regardless of whether it is required
This not only increases the amount of network Io, but also adds relay log writes to the IO thread on the slave side.
Synchronization principle Description
MySQL's replication tracks all changes to the database (updates, deletions, and so on) based on the primary server in the binary log.
MySQL uses 3 threads to complete the replication work, the specific distribution is the Lord 1 related threads, from the top 2 related threads;
The associated thread of the master can be understood as the Binlog dump thread in the output of show processlist on the primary server, the IO from the server, and the
SQL thread;
The master server is created to send content from the Binlog to the slave server. Reads the primary server from the server I/O thread binlog The dump thread sends the
Content and copies the data to a relay log file (Relay-log) from the server data directory, which is used by SQL threads to read the trunk log
and perform the updates that are included in the log.
MySQL's replication is one-way, asynchronous synchronous
The MySQL synchronization mechanism is recorded in the binary log based on the master to update, delete, and so on all databases. Therefore, you want to enable synchronization
mechanism, the binary log must be enabled in master. Each slave accepts update operations that are recorded in the binary log from master.
Therefore, a copy of this operation was performed on the slave. It should be very important to realize that the binary logs are only opened from the binary log enabled
The update operation is only recorded at the beginning of the time. All slave must copy the data that already exists on master when binary logs are enabled.
If the data on the slave is inconsistent with the data on the slave when you run synchronization, then the synchronization fails.
One way to copy the data from master is to execute the LOAD data from master statement on slave. Note, however, that the LOAD DATA from Master is available only after MySQL 4.0.0, and only supports the MyISAM type table on master. In the same way,
This operation requires a global read lock, so that when the log is sent to slave, there will be no update operation on master. When the implementation of the
When the free-lock table is hot-backed (in MySQL 5.0), the global read lock is not necessary. Because of these limitations, we recommend that only the master
The LOAD data from master statement is executed only when the correlation is relatively small, or a long read lock is allowed on MASTER.
Since the speed of the LOAD DATA from MASTER varies between each system, a good measure is the ability to copy 1MB of data per second.
This is only a rough estimate, but both master and slave are Pentium 700MHz machines and can be reached with the 100MBIT/S network connection.
The speed. After the master data has been fully copied on the slave, you can connect to master and wait for the update to be processed. If the master
When the machine or slave connection is disconnected, slave periodically attempts to connect to master until it can reconnect and wait for the update. The retry interval is controlled by the –master-connect-retry option, and its default value is 60 seconds. Each slave records the location of the log when it is closed.
Master does not know how many slave are connected or which slave start updating.

The MySQL sync feature is implemented by 3 threads (1 on Master, 2 on slave). After the START SLAVE statement is executed, SLAVE creates
An I/O thread. The I/O line thread attached to master and requests that master send the statements in the binary log. Master creates a thread to
The contents of the log are sent to the slave. This thread executes the Binlog Dump in the result of the SHOW processlist statement on Master
Threads are. The I/O thread on the slave reads the statements sent by the master's Binlog Dump thread and copies them to its data directory
In the trunk log (relay logs). The third is the SQL thread, which salve uses to read the trunk logs and then executes them to update the data.
As mentioned above, there are 3 threads on each mster/slave. There are multiple threads on each master, which creates a thread for each slave connection, and each slave has only I/O and SQL threads. Prior to MySQL 4.0.2, synchronization required only 2 threads (master and slave each). I/O on the Slave
and the SQL thread is merged into one, it does not use the relay log. The advantage of using 2 threads on a slave is to separate the read log and execution into 2
Independent task. The Read log task does not slow down when performing tasks slowly. For example, if slave stopped for some time, then
The I/O thread can read all logs from Master very quickly after the slave is started, although the SQL thread may fall behind the I/O thread for several hours.
If slave is stopped after the SQL thread has not been fully executed, the I/O thread has read all the update logs and is stored locally
Log (Relay-log), so they will continue to execute after the slave is started again. This allows you to clear the binary on master
Log, because slave has no need to go to master to read the update log. Executing the show Processlist statement tells us what happens on the master and slave that we care about.

Note:
If the primary server restarts, you need to restart the slave from the server to replicate

This article is from the "OpenVPN Build" blog, please be sure to keep this source http://lovesource.blog.51cto.com/1454821/1585131

MySQL Master-slave replication