MySQL Master-slave replication

MySQL cluster, MySQL master-slave architecture, MySQL load balancer

MySQL Master-Slave synchronization
Principle:
Replication Threads
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 the Master, and requests the log content from the specified location (or from the beginning of the log) to the designated log file;
2. When Master receives a request from an IO thread from Slave, the IO thread that is responsible for the replication reads the log information from the specified log at the specified location based on the requested information and returns the IO thread to 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. When the Slave SQL thread detects a new addition to the Relay log, it immediately parses the contents of the log file into those executable query statements that are executed at the Master end, and executes the query itself. This way, the same Query is actually executed on the Master and Slave ends, so the data on both ends is exactly the same
Implementation steps:
Environment:
Master server: 192.168.1.215
Slave Server: 192.168.1.216
The same version of MySQL is installed on both servers.
1. Modify the master server MySQL profile my.cnf, set Server-id to 1, turn on Binlog, and reset Binlog.
Vim/etc/my.cnf
[Mysqld]
........
........
Server-id= 1
Log-bin=mysql-bin
Restart Services: Service MySQL restart
Reset Binlog:mysql>reset Master;
2. Authorize a user to back up from the server to log on to the master server and copy all the databases on the primary server to the slave server.
Authorization: Grant replication Slave on * * to ' wcy ' @ ' 192.168.1.216 ' identified by ' 123456 ';
(You can do a link test on the slave after completing the command from the server terminal: Mysql-h192.168.1.215-uwcy-p, if you cannot connect, check the host firewall or host MySQL port)
To view created users:
> select User.host from Mysql.user;
MySQL lock table read-only (other accounts log on to MySQL can not write table operations, to prevent the backup of the database after the main MySQL table update, resulting from inconsistent with the database content)
> Flush tables with read lock;
To view the lock table countdown time:
> Show variables like '%timeout% ';
------------------------
....
Wait_timeout | 28800
------------------------
All backups of Master's database tables are exported and delivered to the slave server.
#mysqldump-u root-p123456--opt--flush-logs--all-database >/root/allbak.sql
#scp allbak.sql [Email protected]:/root
View MySQL offset (the offset value is incremented if the database has a write operation)
# mysql-u root-p123456-e "Show Master Status"
3, on the slave server
Recovering the server's database to slave
#mysql-U root-p123456 </root/allbak.sql
Vim/etc/my.cnf
[Mysqld]
....
Server-id= 2
Log-bin=mysql-bin
#service MySQL Restart
4, configuration slave
Mysql>slave stop;
mysql> Reset Slave;
Mysql>change Master to master_host= ' 192.168.1.215 ', master_user= ' wcy ', master_password= ' 123456 ', master_port=3306 , master_log_file= ' mysql-bin.000003 ', master_log_pos=106;
Mysql>slave start;
Mysql>show slave status\g;
If these two actions are yes, they will succeed.
Slave_io_running:yes
Slave_sql_running:yes
Finally back to the main MySQL database unlock (mysql master)
# mysql-u root-p123456
> Unlock tables;
Test master/Slave synchronization:
(server)
# mysql-u ROOT-P123456-E "CREATE Database test02;"
# mysql-u ROOT-P123456-E "show databases like ' test02 ';"
------------
+-------------------+
| Database (test02) |
+-------------------+
| test02 |
+-------------------+
------------
(cilent)
# mysql-u ROOT-P123456-E "show databases like ' test02 ';"
-------------
+-------------------+
| Database (test02) |
+-------------------+
| test02 |
+-------------------+
-------------
Test success:

First, the environment
master:192.168.124.51
MySQL version: 5.1.48-community-log
slave:192.168.124.52
MySQL version: 5.1.48-community-log

II. Master/Slave database
Back up the existing database on the host, and then build the database with the same name on the slave and restore it.
(This time it was 51 on the two databases Database1 and DATABASE2)

Iii. related configurations on master and slave
There may be no my.cnf files in the/etc directory, copy my-medium.cnf to/etc from the/user/share/mysql directory and modify it to MY.CNF (as on master and slave).
Like # CP/USER/SHARE/MYSQL/MY-MEDIUM.CNF/ETC/MY.CNF

1. Modify the configuration file my.cnf on master.
Under [Mysqld], add the following fields:
Server-id=1
Log-bin=log
BINLOG-DO-DB=DATABASE1//databases that need to be synchronized
Binlog-do-db=database2
Binlog-ignore-db=mysql//Ignored database

Add a sync account to master slave
Grant Replication Slave on * * to ' affairlog ' @ ' 192.168.124.52 ' identified by ' password ';
Successful landing on the slave
To restart the master MySQL service:
service MySQL restart;

View log conditions with the show Master Status command
Mysql> Show Master Status\g;

1. Row ***************************
file:log.000027
position:3151
Binlog_do_db:database1,database2
binlog_ignore_db:
1 row in Set (0.00 sec)

2. Modify the configuration file my.cnf on the slave.
Under [Mysqld], add the following fields:
server-id=2
master-host=192.168.124.51
Master-user= Affairlog
master-password= Password
master-port=3306
Master-connect-retry=60
REPLICATE-DO-DB=DATABASE1//Synchronized database
Replicate-do-db=database2
Replicate-ignore-db=mysql//Ignored database

To restart the slave MySQL service:
service MySQL restart;

Go to MySQL in the slave machine.
Mysql>start slave;
Mysql>show slave status\g;

1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.124.51
Master_user:affairlog
master_port:3306
Connect_retry:60
master_log_file:log.000027
read_master_log_pos:3151
relay_log_file:localhost-relay-bin.000379
relay_log_pos:245
relay_master_log_file:log.000027
Slave_io_running:yes
Slave_sql_running:yes
Replicate_do_db:database1,database2
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:3151
relay_log_space:543
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:
1 row in Set (0.00 sec)

If the slave_io_running, slave_sql_running status is yes, the setting is successful.

Iv. problems arise
Slave_io_running:no or Slave_sql_running:no
1. Stop slave Service
mysql> slave stop;
Query OK, 0 rows affected (2.01 sec)

2. Workaround
Solution 1
A. View on master.
Mysql> Show Master Status\g;
1. Row ***************************
file:log.000027
position:3151
Binlog_do_db:database1,database2
binlog_ignore_db:
1 row in Set (0.00 sec)
B. Manually sync to slave.
Mysql>change Master to
>master_host= ' 192.168.124.51 ',
>master_user= ' Affairlog ',
>master_password= ' Password ',
>master_log_file= ' log.000027 ',
>master_log_pos=3151;

Query OK, 0 rows Affected (0.00 sec)

Workaround 2
mysql> slave stop;
mysql> SET GLOBAL sql_slave_skip_counter=1;
mysql> slave start;

3. Start the slave service
mysql> slave start;

4. Review the slave_io_running, slave_sql_running status again, and yes to indicate that the setting was successful.

Ps:
Slave_io_running: Connect to the main library and read logs from the main library to local, generate local log files
Slave_sql_running: Reads the local log file and executes the SQL command in the log.

2012.07.06 Supplement: Today encountered from the machine relocation, the server IP changed, although before the master and slave, but later recovery using the above method or did not solve the slave_io_running:no, confirmed several times, later found that the original computer did a firewall restrictions, OH buy karma! Remember, this is experience!
Master-Slave synchronization starting with version 5.1, the configuration file My.cnf file does not support the Master command, only after logging in to the database, the change master to command to configure the corresponding
You can define database and log information for master-slave synchronization in the MY.CNF configuration file.
Primary database MY.CNF configuration file
[Mysqld]
Datadir=/var/lib/mysql
Socket=/var/lib/mysql/mysql.sock
User=mysql
Open_files_limit = 10240
Max_connections = 5000
Max_connect_errors = 6000
Table_cache = 614
external-locking = FALSE
Max_allowed_packet = 32M
Sort_buffer_size = 1M
Join_buffer_size = 1M
Thread_cache_size = 300
#thread_concurrency = 8
Query_cache_size = 512M
Query_cache_limit = 2M
Query_cache_min_res_unit = 2k
Thread_stack = 192K
Transaction_isolation = read-committed
Tmp_table_size = 246M
Max_heap_table_size = 246M
Max_binlog_cache_size = 8M
Max_binlog_size = 1G
Key_buffer_size = 256M
Read_buffer_size = 1M
Read_rnd_buffer_size = 16M
Bulk_insert_buffer_size = 64M
Myisam_sort_buffer_size = 128M
Myisam_max_sort_file_size = 10G
Myisam_repair_threads = 1
Myisam_recover
Interactive_timeout = 120
Wait_timeout = 120
Skip-name-resolve
#slave-skip-errors = 1032,1062,126,1114,1146,1048,139
#log_config
Expire_logs_day=30
Long_query_time = 3
Log-slave-updates
Back_log = 600
Log-error =/var/lib/mysql/log/error_log/mysql_error.log
Log-bin =/var/lib/mysql/log/bin_log/bin.log
Binlog_cache_size = 4M
Log-slow-queries =/var/lib/mysql/log/slow_query_log/slow.log
Long_query_time = 5
#general_log = 1
#general_log_file =/var/lib/mysql/log/query_log/query.log
Character_set_server=utf8
Server-id=1
binlog-do-db=test01
Binlog-ignore-db=mysql
# Default to using old password format for compatibility with MySQL 3.x
# clients (those using the Mysqlclient10 compatibility package).
Old_passwords=1
# Disabling Symbolic-links is recommended to prevent assorted security risks;
# to does so, uncomment the line:
# symbolic-links=0
[Mysqld_safe]
Pid-file=/var/run/mysqld/mysqld.pid
[Client]
#default-character-set=utf8
[MySQL]
Default-character-set=utf8

MY.CNF configuration file from the database server
[Client]
#password = Your_password
Port = 3306
Socket =/tmp/mysql.sock

# The MySQL server
[Mysqld]
#datadir =/usr/local/mysql/data
Port = 3306
Socket =/tmp/mysql.sock
Table_open_cache = 64
Net_buffer_length = 8K
server-id=2
Open_files_limit = 10240
Max_connections = 5000
Max_connect_errors = 6000
Table_cache = 614
external-locking = FALSE
Max_allowed_packet = 32M
Sort_buffer_size = 1M
Join_buffer_size = 1M
Thread_cache_size = 300
#thread_concurrency = 8
Query_cache_size = 512M
Query_cache_limit = 2M
Query_cache_min_res_unit = 2k
Thread_stack = 192K
Transaction_isolation = read-committed
Tmp_table_size = 246M
Max_heap_table_size = 246M
Max_binlog_cache_size = 8M
Max_binlog_size = 1G
Key_buffer_size = 256M
Read_buffer_size = 1M
Read_rnd_buffer_size = 16M
Bulk_insert_buffer_size = 64M
Myisam_sort_buffer_size = 128M
Myisam_max_sort_file_size = 10G
Myisam_repair_threads = 1
Myisam_recover
Interactive_timeout = 120
Wait_timeout = 120
Skip-name-resolve
#slave-skip-errors = 1032,1062,126,1114,1146,1048,139
#log_config
Expire_logs_day=30
Long_query_time = 3
Log-slave-updates
Back_log = 600
Log-error =/usr/local/mysql/data/log/mysql_error.log
Log-bin =/usr/local/mysql/data/log/bin_log/bin.log
Binlog_cache_size = 4M
Binlog_format = MIXED
Relay-log-index =/usr/local/mysql/data/log/relay_log/relaylog.index
Relay-log-info-file =/usr/local/mysql/data/log/relay_log/relay_log_info
Relay-log =/usr/local/mysql/data/log/relay_log/relay.log
Log-slow-queries =/usr/local/mysql/data/log/slow_query_log/slow.log
Long_query_time = 5
General_log = 1
General_log_file =/usr/local/mysql/data/log/query_log/query.log
#master-host=10.69.160.4
#master-user=slave
#master-password=root
#master-port=3306
#master-connect-retry=5
replicate-do-db=test01
Replicate-ignore-db=mysql
#log-bin
#expire_logs_day =30
#general_log = 1
#general_log_file =/usr/local/mysql/data/mysql_query.log
#skip-networking
# Replication Master Server (default)
# Binary logging is required for replication
#log-bin=mysql-bin
# Binary Logging format-mixed recommended
#binlog_format =mixed
# required Unique ID between 1 and 2^32-1
# defaults to 1 if master-host are not set
# but would not function as a master if omitted
#server-id = 1
[Mysqldump]
Quick
Max_allowed_packet = 16M
[MySQL]
No-auto-rehash
# Remove The next comment character if you is not a familiar with SQL
#safe-updates
[Myisamchk]
Key_buffer_size = 20M
Sort_buffer_size = 20M
Read_buffer = 2M
Write_buffer = 2M
[Mysqlhotcopy]
Interactive-timeout

This article from "Banging Blog" blog, reproduced please contact the author!

MySQL Master-slave replication