MySQL Master-slave replication

First, the experimental environment

System: centos6.5

Software: mysql-5.5.32.tar.gz

Second, the experimental steps

1. Basic concept and principle of master-slave replication
   

MySQL Replication is an asynchronous copy process, copied from one MySQL instace (we call Master) to another MySQL instance (we call it Slave). The entire replication process between Master and Slave is done primarily by three threads, of which two threads (SQL thread and IO thread) are on the Slave side, and another thread (IO thread) on the master side.

To implement MySQL Replication, you must first turn on the binary Log (mysql-bin.xxxxxx) function on the Master side, otherwise it will not be possible. Because the entire replication process is actually a variety of operations that are logged in the execution log that slave obtains the log from the master side and then executes it in its own full sequence. The Binary Log for MySQL can be opened by using the "-log-bin" parameter option in the process of starting MySQL Server, or by adding "Log-bi" in the mysqld parameter group in the MY.CNF configuration file (the Parameters section after the [mysqld] identity) N "parameter entry.

The basic process for MySQL replication is as follows:

1) Slave the above IO line thread attached to Master, and requests the log content from the specified location (or from the beginning of the log file);

2) Master receives a request from an IO thread from the Slave, reads the log information from the specified log at the specified location by the IO thread responsible for the replication, and returns it to the IO thread on the Slave side. In addition to the information contained in the log, the return information includes the name of the binary log file on the Master side of the returned information and its location in binary log;

3) After the Slave IO thread receives the information, it writes the received log content to the end of the relay log file (mysql-relay-bin.xxxxxx) on the Slave side, And the read to the master side of the Bin-log's file name and location to the Master-info file, so that the next time you read the high-speed master "I need to start from somewhere in the Bin-log log content, please send me"

4) Slave's SQL thread detects that the content in the Relay log has been added, it immediately resolves the contents of the log file into those executable query statements that were executed at the Master end, and executes the query itself. In this way, the same Query is actually executed on the Master and Slave ends, so the data on both ends is exactly the same.

Refer to above: http://machael.blog.51cto.com/829462/239112/

650) this.width=650; "alt=" \ "\" "src="%5c "/>

2. Master-slave Replication Experiment Steps

This experiment uses the multi-instance to realize the master-slave replication. This experiment requires two MySQL database server master and slave,master primary server, slave from the server, the initial state, master and slave in the same data information, when the data in master changes, Slave also follow the corresponding changes, so that the master and slave data information synchronization, to achieve the purpose of backup.

MySQL master and slave configuration steps:
1) Prepare two database environments, or a single multi-instance environment, to start and log in normally.
2) Configure the MY.CNF file, the main library configuration log-bin and Server-id parameters, from the library configuration Server-id, not the same as the main library and other libraries, generally do not open from the library log-bin function. Note: After configuring the parameters, reboot takes effect.

Master:log-bin =/data/3306/mysql-bin

Server-id=1

Slave:server-id=3

3) Log in to the main library add the account used to sync the main library from the library for example: Rep, and authorize replication slave sync permissions

Grant Replication Slave on * * to ' rep ' @ ' 10.0.0.% ' identified by ' Chen ';
Flush privileges;

4) Log in to the main library the entire Library lock table flush table with read lock and then view Show Master Status View Binlog's position parameter status

+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|      mysql-bin.000001 |              639 |                  | |
+------------------+----------+--------------+------------------+

5) The new Windows Linux command line backs up or exports the original database data and copies it to the server directory from which the library resides if the volume of data is large and allows downtime to be packaged without mysqldump
6) Unlock the main library, unlock tables;
7) Restore the original data exported from the main library to the library
8) View the location status of the Binlog based on show master status of the main library, and perform the change master to ... from the library.
9) Open the sync switch from the library, start slave
Start slave
10) Check the sync status from the library show slave status\\g and perform the update test in the main library.
Mysql> Show Slave Status\\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:10.0.0.2
Master_user:rep
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000001
read_master_log_pos:639
relay_log_file:relay-bin.000002
relay_log_pos:425
relay_master_log_file:mysql-bin.000001
Slave_io_running:yes
Slave_sql_running:yes
replicate_do_db:
Replicate_ignore_db:mysql
Replicate_do_table:
Replicate_ignore_table:
Replicate_wild_do_table:
Replicate_wild_ignore_table:
last_errno:0
Last_error:
skip_counter:0
exec_master_log_pos:639
relay_log_space:575
Until_condition:none
Until_log_file:
until_log_pos:0
Master_ssl_allowed:no
Master_ssl_ca_file:
Master_ssl_ca_path:
Master_ssl_cert:
Master_ssl_cipher:
Master_ssl_key:
seconds_behind_master:0
Master_ssl_verify_server_cert:no
last_io_errno:0
Last_io_error:
last_sql_errno:0
Last_sql_error:
Replicate_ignore_server_ids:
Master_server_id:1
1 row in Set (0.00 sec)

Mysql>

After completing the appeal steps, the master-slave synchronization of the data is realized, that is, the library tables are created in the main library, but in real time from the library.
To learn more about MySQL multiple instances click: http://purify.blog.51cto.com/10572011/1795031

This article is from "Wake up your not alarm clock but dream" blog, please be sure to keep this source http://purify.blog.51cto.com/10572011/1795297

MySQL Master-slave replication