MySQL5.6 Master-slave replication (read/write separation)

MySQL Master-slave replication (read-write separation) and cluster differences:

1, Master-slave replication (read and write separation): Generally need two and above the database server (one for writing data, one for synchronizing the master data and for data query operations).
Limitations:
(1) After the configuration of the master-slave replication, the same table, only one server write operations. If a write operation is performed from the top, and then the master operates the table, or causes the master to never synchronize, it is said that it can be configured as the main master mode
(2) The primary database server is down and the business system needs to be manually switched to the slave database server. High availability is not possible (unless the deployment of keepalive is made into a highly available scenario).
2, the cluster is composed of n database server, the data write and query is random to any database server, the other database server will automatically synchronize the operation of the database.
Any database outage does not have a large impact on the entire cluster.
Limitations: I have been tested to know that the MySQL cluster version (MySQL Cluster) can only synchronize the data of the NDB storage engine, if it is a innodb or other MySQL storage engine. This also led me to abandon the application of this scheme in the business system.
##################### #接下来开始MySQL5.6.12 Master-slave copy tutorial:
1, MySQL5.6 start master-slave replication in two ways: based on the log (Binlog); Gtid (global transaction identifier).
Note: The Gtid method does not support temporary tables! So if your business system is going to use a temporary table, do not consider this way, at least the latest version of MySQL5.6.12 Gtid replication does not support the temporary table.
So this tutorial is mainly to tell you how to copy from the log (Binlog) way!

2. mysql official MySQL replication tutorial:
Http://dev.mysql.com/doc/refman/5.6/en/replication.html
This official tutorial is highly recommended for everyone to read (requires a certain amount of English reading ability Oh!) No, just Google Translate and then read it ~).

3. Preparatory work:
(1) Before you configure MySQL master-slave replication (read and write separation), you need to install the MySQL5.6 on both the master and slave servers first.
(2) The current MySQL5.6 GA version is MySQL5.6.12 (click here to download the MySQL5.6.12 source package).
Personally recommended Linux (Redhat/centos 6.4) source code compilation installation, the specific can see this tutorial: Redhat/centos source code compiled installation MySQL5.6.12
(3) Note:
(a) If you need to use a production environment, do not be anxious to do a MySQL boot operation when installing MySQL in a tutorial. It is recommended to delete MySQL initialization generated/usr/local/mysql/mysql.cnf and then put your optimized MySQL configuration file my.cnf into/etc.
(b) It is recommended to host two servers on the same LAN, and to master and prepare two database networks for interoperability.
(4) My environment:
Primary Database ip:192.168.100.2
From the database ip:192.168.100.3
4. Modify the configuration file of the master database:
[Mysqld]
Server-id=1
Log-bin=mysqlmaster-bin.log
Sync_binlog=1
#注意: The following parameter needs to be modified to about 70% of the server's memory
Innodb_buffer_pool_size = 512M
Innodb_flush_log_at_trx_commit=1
Sql_mode=strict_trans_tables,no_auto_create_user,no_engine_substitution,no_auto_value_on_zero
Lower_case_table_names=1
Log_bin_trust_function_creators=1
To restart MySQL after modification:/etc/init.d/mysql restart
--------------------------------------------------------
Optimized configuration file Master-my.cnf
# for advice The change settings
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[Client]
port=3306
Socket=/usr/local/mysql/mysql.sock
Default-character-set=utf8

[Mysqld]
Sync_binlog=1
Server-id=1
port=3306
Socket=/usr/local/mysql/mysql.sock
Pid-file=/home/mysql/temp/my3306.pid
User=mysql
Datadir=/home/mysql/data
tmpdir=/home/mysql/temp/
Log-bin=/home/mysql/data/mysqlmaster-bin
Log-error=/home/mysql/logs/error.log
Slow_query_log_file=/home/mysql/logs/slow.log
Binlog_format=mixed
Slow_query_log
long_query_time=10
wait_timeout=31536000
interactive_timeout=31536000
max_connections=500
max_user_connections=490
max_connect_errors=2
Character_set_server=utf8
Skip-external-locking
Key_buffer_size = 128M
Max_allowed_packet = 5M
Table_open_cache = 512
Sort_buffer_size = 2M
Read_buffer_size = 2M
Read_rnd_buffer_size = 8M
Myisam_sort_buffer_size = 64M
Thread_cache_size = 8
Query_cache_size = 32M
# Try number of CPU ' s*2 for thread_concurrency
Thread_concurrency = 4
Binlog-ignore-db=mysql
Binlog-ignore-db=information_schema
Replicate_ignore_db=mysql
Replicate_ignore_db=information_schema
expire-logs-days=10
Skip-slave-start
Skip-name-resolve
Lower_case_table_names=1
Log_bin_trust_function_creators=1

# InnoDB
Innodb_data_home_dir=/home/mysql/data
Innodb_log_group_home_dir=/home/mysql/logs
Innodb_data_file_path=ibdata1:128m:autoextend
innodb_buffer_pool_size=2g
innodb_log_file_size=10m
Innodb_log_buffer_size=8m
Innodb_lock_wait_timeout=50
Innodb_file_per_table
Innodb_flush_log_at_trx_commit=1

#sql_mode =no_engine_substitution,strict_trans_tables
Sql_mode=strict_trans_tables,no_auto_create_user,no_engine_substitution,no_auto_value_on_zero

[Mysqldump]
Quick
Max_allowed_packet = 16M

[MySQL]
No-auto-rehash

[Myisamchk]
Key_buffer_size = 256K
Sort_buffer_size = 256K
Read_buffer = 256K
Write_buffer = 256K

[Mysqlhotcopy]
Interactive-timeout
------------------------------------------------

5. Modify the configuration file from the database (Server-id is configured to a number greater than 1):
[Mysqld]
server-id=2
Log-bin=mysqlslave-bin.log
Sync_binlog=1
#注意: The following parameter needs to be modified to about 70% of the server's memory
Innodb_buffer_pool_size = 512M
Innodb_flush_log_at_trx_commit=1
Sql_mode=strict_trans_tables,no_auto_create_user,no_engine_substitution,no_auto_value_on_zero
Lower_case_table_names=1
Log_bin_trust_function_creators=1

Restart Mysql:/etc/init.d/mysql Restart after modification
Optimized from database configuration file: slave-my.cnf
# for advice The change settings
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[Client]
port=3306
Socket=/usr/local/mysql/mysql.sock
Default-character-set=utf8

[Mysqld]
Sync_binlog=1
server-id=2
port=3306
Socket=/usr/local/mysql/mysql.sock
Pid-file=/home/mysql/temp/my3306.pid
User=mysql
Datadir=/home/mysql/data
tmpdir=/home/mysql/temp/
Log-bin=/home/mysql/data/mysqlslave-bin
Log-error=/home/mysql/logs/error.log
Slow_query_log_file=/home/mysql/logs/slow.log
Binlog_format=mixed
Slow_query_log
long_query_time=10
wait_timeout=31536000
interactive_timeout=31536000
max_connections=500
max_user_connections=490
max_connect_errors=2
Character_set_server=utf8
Skip-external-locking
Key_buffer_size = 128M
Max_allowed_packet = 5M
Table_open_cache = 512
Sort_buffer_size = 2M
Read_buffer_size = 2M
Read_rnd_buffer_size = 8M
Myisam_sort_buffer_size = 64M
Thread_cache_size = 8
Query_cache_size = 32M
# Try number of CPU ' s*2 for thread_concurrency
Thread_concurrency = 4
Binlog-ignore-db=mysql
Binlog-ignore-db=information_schema
Replicate_ignore_db=mysql
Replicate_ignore_db=information_schema
expire-logs-days=10
#skip-slave-start
Skip-name-resolve
Lower_case_table_names=1
Log_bin_trust_function_creators=1

# InnoDB
Innodb_data_home_dir=/home/mysql/data
Innodb_log_group_home_dir=/home/mysql/logs
Innodb_data_file_path=ibdata1:128m:autoextend
innodb_buffer_pool_size=2g
innodb_log_file_size=10m
Innodb_log_buffer_size=8m
Innodb_lock_wait_timeout=50
Innodb_file_per_table
Innodb_flush_log_at_trx_commit=1

#sql_mode =no_engine_substitution,strict_trans_tables
Sql_mode=strict_trans_tables,no_auto_create_user,no_engine_substitution,no_auto_value_on_zero

[Mysqldump]
Quick
Max_allowed_packet = 16M

[MySQL]
No-auto-rehash

[Myisamchk]
Key_buffer_size = 256K
Sort_buffer_size = 256K
Read_buffer = 256K
Write_buffer = 256K

[Mysqlhotcopy]
Interactive-timeout

6. SSH login to the main database:
(1) Create an account for master-slave replication on the primary database (192.168.100.3 exchange your slave database IP):
Mysql-uroot-p

Mysql>
GRANT REPLICATION SLAVE on * * to ' repl ' @ ' 192.168.100.3 ' identified by ' repl ';
(2) Primary database lock table (no re-inserting data to get the binary log coordinates of the primary database):
Mysql>
FLUSH TABLES with READ LOCK;
(3) Then clone an SSH session window and open the MySQL command line in this window:
Mysql-uroot-p

Mysql>
SHOW MASTER STATUS;
+------------------------+----------+--------------+------------------+-------------------+

|
File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

+------------------------+----------+--------------+------------------+-------------------+
|
mysqlmaster-bin.000001 |              332 |                  |                   | |
+------------------------+----------+--------------+------------------+-------------------+

Row in Set (0.00 sec)
Mysql>
Exit

In this example, the binary log file is mysqlmaster-bin.000001, the location is 332, and the two values are recorded, which will be used later.
(4) Create a data snapshot using the mysqldump command on the primary database:
Mysqldump-uroot-p-h127.0.0.1-p3306--all-databases--triggers--routines--events >all.sql
Next, you will be prompted to enter the root password of the MySQL database, and if the current database is small, you can export it soon.
(5) Jian Di (2) Step Master Data lock table operation:
Mysql>
UNLOCK TABLES;
7. SSH login to the slave database:
(1) Upload the primary database snapshot All.sql from the previous backup to a path from the database via FTP, SFTP, or by other means, such as I put in the/home/yimiju/directory;
(2) From the Import master snapshot:
Cd/home/yimiju
Mysql-uroot-p-h127.0.0.1-p3306 < All.sql

Next you will be prompted to enter the root password of the MySQL database, after the input is completed, if the current database is not large, will soon be able to import completed.
(3) To set the primary database information to be replicated from the database (note the values of Master_log_file and Master_log_pos are modified):
Mysql-uroot-p

Mysql>
Change MASTER to master_host= ' 192.168.100.2 ', master_user= ' repl ', master_password= ' repl ', master_log_file= ' Mysqlmaster-bin.000001 ', master_log_pos=332;

#
Then start the replication thread from the database:

Mysql>
START slave;

Then query the slave state of the database:

Mysql>
SHOW slave STATUS \g

If the following two parameters are all Yes, then the master-slave configuration succeeds!

Slave_io_running:
Yes

Slave_sql_running:
Yes
(4) Next you can create a database, table, insert data on the primary database, and then see if the operations are synchronized from the database.

######################## #CentOS 6.3 under MySQL Master Master Interop architecture Configuration #############################
The MySQL Master Master Interop Architecture is based on the MySQL incremental log, which differs from the master-slave replication structure.

In the primary master replication structure, any one of the two servers on the above data inventory changes will be synchronized to another server, the change is based on changes in SQL statements, if the deletion of the system database source files or deleted after the new creation of the same name MySQL table implementation synchronization is invalid. This allows the two servers to master each other and provide services out-of-the-way, which provides better performance than using master-slave replication.

Next I will use two identical environments to achieve this effect:

System Environment: CentOS6.3

Database: mysql-5.6.10

Installation configuration mysql (slightly)

1. Master 1 MySQL configuration: (192.168.7.201)

Delete previously Legacy bin logs

# rm-rf/usr/local/mysql/log/bin*

To modify the MySQL configuration file:

# VI/ETC/MY.CNF

Add to:

-----------------

# Replication Master Server 1

# Bin Log Path

Log-bin =/usr/local/mysql/log/bin.log

# Server ID Number

Server-id = 1

# Ignore MySQL database replication

Binlog-ignore-db=mysql

# 2 Increase each time

Auto-increment-increment = 2

# Set the offset of the automatically growing field, which is the initial value of 2

Auto-increment-offset = 1

------------

# mysql-u root-p123456

Add a sync account to primary 2 on main 1

> Grant replication Slave on * * to ' slave ' @ ' 192.168.7.249 ' identified by ' 123456 ';

All of the primary 1 database tables are backed up and sent to the primary 2 server.

#/usr/local/mysql/bin/mysqldump-u root-p123456--opt--skip-lock-tables--flush-logs--all-database >/root/allbak . sql

# CD ~

# SCP Allbak.sql [email protected]:/root

Restart Service

# Service Mysqld Restart

# mysql-u root-p123456;

Configure the connection to the primary 2 server (note that this step must be performed after you add the account to the main 2)

> Stop slave;

> Change Master to master_host= ' 192.168.7.249 ', master_user= ' slave ', master_password= ' 123456 ';

> Start slave;

2. Master 2 mysql configuration (192.168.7.249)

Delete previously Legacy bin logs

# rm-rf/usr/local/mysql/log/bin*

To modify the MySQL configuration file:

# VI/ETC/MY.CNF

Add to:

--------------------

# Replication Master Server 2

# Bin Log Path

Log-bin =/usr/local/mysql/log/bin.log

server-id=2

# Ignore MySQL database replication

Replicate-ignore-db=mysql

# 2 Increase each time

Auto-increment-increment = 2

# Set the offset of the automatically growing field, which is the initial value of 2

Auto-increment-offset = 2

---------------------

# mysql-u root-p123456

Add a sync account to primary 1 on main 2

> Grant replication Slave on * * to ' slave ' @ ' 192.168.7.201 ' identified by ' 123456 ';

Recovering the primary 1 database to the primary 2

#/usr/local/mysql/bin/mysql-u root-p123456 </root/allbak.sql

Restart Service

# Service Mysqld Restart

# mysql-u root-p123456;

Configuring connections to the Primary 1 server

> Stop slave;

> Change Master to master_host= ' 192.168.7.201 ', master_user= ' slave ', master_password= ' 123456 ';

> Start slave;

Finally, log in two server MySQL background to view the primary primary backup connection status

# mysql-u root-p123456;

> Show slave status\g;

Search for these three lines, and the Master Master Interop configuration succeeds as follows

Slave_io_state:waiting for Master to send event

Slave_io_running:yes

Slave_sql_running:yes

When the primary 1 database is added, deleted, and the data is changed, the Master 2 is also updated synchronously.

When the primary 2 database is added, deleted, and the data is changed, the Master 1 is also updated synchronously.

This can be combined with keepalived to achieve database dual-machine hot standby + data synchronization interoperability, greatly improving the reliability and security of MySQL.

This article describes how to cooperate with the former to achieve keepalived dual-machine hot standby

System environment: CentOS 6.3 x64

MySQL version: mysql-5.6.10

Keepalived version: keepalived-1.2.7



mysql-vip:192.168.7.253

mysql-master1:192.168.7.201

mysql-master2:192.168.7.249



1. keepalived installation and configuration on the mysql-master1:192.168.7.201 server



Compile and install, the actual kernel version of the machine is subject to



# wget http://www.keepalived.org/software/keepalived-1.2.7.tar.gz

# tar ZXVF keepalived-1.2.7.tar.gz

# CD keepalived-1.2.7

#./configure--prefix=/usr/local/keepalived--with-kernel-dir=/usr/src/kernels/2.6.32-

279.el6.x86_64

# Make && make install

Create a new profile, the default keepalived boot will go to the/etc/keepalived directory to find the configuration file

# mkdir/etc/keepalived

# vi/etc/keepalived/keepalived.conf

------------------

Global_defs {

Notification_email {

[Email protected]

}

Notification_email_from [email protected]

Smtp_server stmp.126.com

Smtp_connect_timeout 30

router_id mysql-ha

}

Vrrp_instance vi_1{

# Two configurations here are backup

State BACKUP

# Note the NIC interface

Interface eth0

VIRTUAL_ROUTER_ID 51

# priority, another set to 90

Priority 100

Advert_int 1

# do not proactively preempt resources

Nopreempt

Authentication {

Auth_type PASS

Auth_pass 1111

}

virtual_ipaddress {

192.168.7.253

}

}

Virtual_server 192.168.7.253 3306 {

# Check the Real_server status once every 2 seconds

Delay_loop 2

# LVS algorithm

Lb_algo WRR

# LVS Mode

Lb_kind DR

# Session Hold Time
Persistence_timeout 60

Protocol TCP

Real_server 192.168.7.201 3306 {

Weight 3

# Script executed after service down detected

notify_down/etc/rc.d/keepalived.sh

Tcp_check {

# Connection Time-out

Connect_timeout 10

# Number of re-connect

Nb_get_retry 3

# re-connect interval time

Delay_before_retry 3

# Health Check Port

Connect_port 3306

}

}

----------------------

Script to execute after writing detection service down



# vi/etc/rc.d/keepalived.sh

-------------

#!/bin/sh

Pkill keepalived

-------------

# chmod +x/etc/rc.d/keepalived.sh

Note: This script is used by the configuration file Notify_down option above, and keepalived uses the Notify_down option to check the service status of Real_server.

This script is triggered when a Real_server service failure is found.

As we can see, the script is a command:

The Pkill keepalived force kills the keepalived process, thus realizing the automatic transfer of MySQL fault.

In addition, we do not have to worry about two MySQL will also provide data update operation, because each MySQL on the keepalived configuration only native MySQL Ip+vip, not two MySQL ip+vip.



Start keepalived

#/usr/local/keepalived/sbin/keepalived-d

View connection Status

# Ps-aux | grep keepalived

Return:

---------------

Root 25348 0.0 0.0 36792 720? Ss 07:15 0:00

/usr/local/keepalived/sbin/keepalived-d

Root 25349 0.5 0.1 40968 2016? S 07:15 0:00

/usr/local/keepalived/sbin/keepalived-d

Root 25350 0.0 0.1 40968 1340? S 07:15 0:00

/usr/local/keepalived/sbin/keepalived-d

--------------------

Test

Find a local area network PC, and then go to ping the MySQL VIP, this time the MySQL VIP is able to ping the pass of

Stop the MySQL service to see if the keepalived Health Check program will trigger the script we wrote to kill the keepalived process

# Service Mysqld Stop

# Ps-aux | grep keepalived

This article from "My Sky" blog, declined reprint!

MySQL5.6 Master-slave replication (read/write separation)