|
|
内容：
1、mysql的复制类型
2、mysql的主从复制、主主复制示例
3、mysql的读写分离
一、mysql复制类型
1 复制概述
Mysql内建的复制功能是构建大型，高性能应用程序的基础。将Mysql的数据分布到多个系统上去，这种分布的机制，是通过将Mysql的某一台主机的数据复制到其它主机（slaves）上，并重新执行一遍来实现的。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。
请注意当你进行复制时，所有对复制中的表的更新必须在主服务器上进行。否则，你必须要小心，以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。
2、MySQL Replication Cluster的几种同步数据方式：
Synchronous Replication 同步复制
Asynchronous Replication 异步复制
Semisynchronous Replication 半同步复制
同步复制: 指的是客户端连接到MySQL主服务器写入一段数据, MySQL主服务器同步给MySQL从服务器需要等待从服务器发出同步完成的响应才返回客户端OK, 这其中等待同步的过程是阻塞的, 如果有N台从服务器, 效率极低
异步复制: 指的是客户端连接到MySQL主服务器写入一段数据, MySQL主服务器将写入的数据发送给MySQL从服务器, 然后直接返回客户端OK, 可能从服务器的数据会和主服务不一致
半同步复制:指的是客户端连接到MySQL主服务器写入一段数据, MySQL主服务器只将数据同步复制给其中一台从服务器, 半同步复制给其他的从服务器, 来达到其中一台从服务器完全同步的效果
二、mysql复制示例
1、mysql的主从复制：
master端：
(1)、在[mysqld]段启用二进制日志,并选择唯一的server-ID
1
2
3
4
5
6
7
8
9
10
11
12
13
| [iyunv@localhost ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
log-bin = master log #启动bin日志
log-bin-index = log.index
server-id = 1 #设置serverID
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
|
(2)创建具有复制权限REPLICATION SLAVE,REPLICATION CLIENT的用户
1
| mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'test'@'%' IDENTIFIED BY '123456';
|
(3)查看当前msater的binary-log信息
1
2
3
4
5
6
7
8
9
10
| mysql> SHOW MASTER STATUS/G
-> \G
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/G' at line 1
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: master log.000003 #设置从服务器是需要用到此参数
Position: 343 #设置从服务器是需要用到此参数
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
|
salve端：
(1)修改mysql的配置文件，在[mysqld]段启用relay中继日志,并选择唯一的server-ID，同时设置mysql只读，注意不需要开启binary-log：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| [iyunv@localhost ~]# cat !$
cat /etc/my.cnf
[mysqld]
datadir=/mysql/mydata
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
relay-log = relay-log #开启中继日志
read-only = 1 #设置只读
server-id = 12 #设置serverID
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
|
(2)连接至主服务器，并登录帐号开始复制数据
1
2
3
| mysql> CHANGE MASTER TO MASTER_HOST='10.1.252.235', MASTER_LOG_FILE='master-log.000001', MASTER_LOG_POS=106, MASTER_USER='test', MASTER_PASSWORD='123456';
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.30 sec)
|
(3)查看slave状态，此时slave的复制还没启动
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
| mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.1.252.235
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000001
Read_Master_Log_Pos: 106
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-log.000001
Slave_IO_Running: No #IO-thread还没启动
Slave_SQL_Running: No #SQL-thread还没启动
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 106
Relay_Log_Space: 106
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: NULL
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)
|
(4)启动slave
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
| mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.252.235
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000001
Read_Master_Log_Pos: 106
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 252
Relay_Master_Log_File: master-log.000001
Slave_IO_Running: Yes #已经启动复制
Slave_SQL_Running: Yes #已经启动复制
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 106
Relay_Log_Space: 401
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)
|
(5)测试：
在主服务器创建一个数据库：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: master-log.000001
Position: 106
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
mysql> CREATE DATABASE mysqlreplicaton;
Query OK, 1 row affected (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mysqlreplicaton |
| test |
+--------------------+
4 rows in set (0.00 sec)
|
查看从服务器已然成功同步
1
2
3
4
5
6
7
8
9
10
| mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mysqlreplicaton |
| test |
+--------------------+
4 rows in set (0.00 sec)
|
2、mysql的半同步复制示例
mysql的半同步复制是google提供的semisync_master来提供，所以需要安装次模块：
On Master
1
2
3
4
5
| mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; #加载模块
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1; #启动该模块
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000; #设置连接超时时间
查看主服务器上的semi_sync是否开启，注意clients 变为1 ，证明主从半同步复制连接成功:
mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';
|
On Slave
1
2
3
4
5
| mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; #加载模块
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1; #启动该模块
mysql> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD; #重启线程才能生效
查看从服务器上的semi_sync是否开启:
mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';
|
以上的加载模块只是暂时加载，要想永久加载，要在Master和Slave的my.cnf中编辑：
# On Master
1
2
3
| [mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000 # 1 second
|
# On Slave
1
2
| [mysqld]
rpl_semi_sync_slave_enabled=1
|
3、mysql主主复制示例：
(1)修改配置文件：
# 主服务器上
1
2
3
4
5
6
7
| [mysqld]
server-id = 10
log-bin = mysql-bin
relay-log = relay-mysql
relay-log-index = relay-mysql.index
auto-increment-increment = 2
auto-increment-offset = 1
|
查看bin日志信息
1
2
3
4
5
6
7
| server1|mysql> SHOW MASTER STATUS\G
************************** 1. row ***************************
File: mysql-bin.000001
Position: 710
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
|
# 从服务器上
1
2
3
4
5
6
7
| [mysqld]
server-id = 20
log-bin = mysql-bin
relay-log = relay-mysql
relay-log-index = relay-mysql.index
auto-increment-increment = 2
auto-increment-offset = 2
|
查看bin日志信息：
1
2
3
4
5
6
7
8
| server2|mysql> SHOW MASTER STATUS\G
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 811
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
|
(2)各服务器接下来指定对另一台服务器为自己的主服务器即可：
MASTER:
1
| mysql> CHANGE MASTER TO MASTER_HOST='10.1.252.235', MASTER_LOG_FILE='mysql-log.000003', MASTER_LOG_POS=811, MASTER_USER='test', MASTER_PASSWORD='123456';
|
SLAVE:
1
| mysql> CHANGE MASTER TO MASTER_HOST='10.1.252.235', MASTER_LOG_FILE='mysql-log.000001', MASTER_LOG_POS=710, MASTER_USER='test', MASTER_PASSWORD='123456';
|
更多文章请关注我的博客
三、mysql读写分离示例：
1、mysql的读写分离实验示意图：
2、实现mysql的读写分离的工具有很多，如mysql-mmm，amoeba，mysql-proxy等，这里演示的mysql官方提供的mysql-proxy。注意，mysql-proxy本身并不支持读写分离，mysql-proxy依附了lua读写分离的脚本才能真正实现读写分离，所以，启动proxy时必须指定lua脚本。
3、安装mysql-proxy，mysql-proxy已在epel中提供，所以可以直接yum安装：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
| [20:34 root@centos6.8~]# yum info mysql-proxy
Loaded plugins: fastestmirror, refresh-packagekit, security
Repository 'base' is missing name in configuration, using id
Repository epel is listed more than once in the configuration
Loading mirror speeds from cached hostfile
base | 4.0 kB 00:00
epel | 4.3 kB 00:00
Available Packages
Name : mysql-proxy
Arch : i686
Version : 0.8.5
Release : 1.el6
Size : 218 k
Repo : epel
Summary : A proxy for the MySQL Client/Server protocol
URL : http://forge.mysql.com/wiki/MySQL_Proxy
License : GPLv2
Description : MySQL Proxy is a simple program that sits between your client and MySQL
: server(s) that can monitor, analyze or transform their communication.
: Its flexibility allows for unlimited uses, common ones include: load balancing,
: fail-over, query analysis, query filtering and modification and many more.
[20:42 root@centos6.8~]# yum install -y mysql-proxy
|
4、mysql-proxy的使用：
mysql-proxy的配置可以直接嵌套在mysql的主配置文件my.cfg中，并以[mysql-proxy]段进行填写，还可以直接使用mysql-proxy命令来运行，
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
| [20:45 root@centos6.8~]# mysql-proxy --help-all
Usage:
mysql-proxy [OPTION...] - MySQL Proxy
Help Options:
-h, --help Show help options
--help-all Show all help options
--help-proxy Show options for the proxy-module
proxy-module
-P, --proxy-address=<host:port> listening address:port of the proxy-server (default: :4040)
-r, --proxy-read-only-backend-addresses=<host:port> address:port of the remote slave-server (default: not set)
-b, --proxy-backend-addresses=<host:port> address:port of the remote backend-servers (default: 127.0.0.1:3306)
--proxy-skip-profiling disables profiling of queries (default: enabled)
--proxy-fix-bug-25371 fix bug #25371 (mysqld > 5.1.12) for older libmysql versions
-s, --proxy-lua-script=<file> filename of the lua script (default: not set)
--no-proxy don't start the proxy-module (default: enabled)
--proxy-pool-no-change-user don't use CHANGE_USER to reset the connection coming from the pool (default: enabled)
--proxy-connect-timeout connect timeout in seconds (default: 2.0 seconds)
--proxy-read-timeout read timeout in seconds (default: 8 hours)
--proxy-write-timeout write timeout in seconds (default: 8 hours)
Application Options:
-V, --version Show version
--defaults-file=<file> configuration file
--verbose-shutdown Always log the exit code when shutting down
--daemon Start in daemon-mode
--user=<user> Run mysql-proxy as user
--basedir=<absolute path> Base directory to prepend to relative paths in the config
--pid-file=<file> PID file in case we are started as daemon
--plugin-dir=<path> path to the plugins
--plugins=<name> plugins to load
--log-level=(error|warning|info|message|debug) log all messages of level ... or higher
--log-file=<file> log all messages in a file
--log-use-syslog log all messages to syslog
--log-backtrace-on-crash try to invoke debugger on crash
--keepalive try to restart the proxy if it crashed
--max-open-files maximum number of open files (ulimit -n)
--event-threads number of event-handling threads (default: 1)
--lua-path=<...> set the LUA_PATH
--lua-cpath=<...> set the LUA_CPATH
|
5、根据上面的命令帮助，我们来使用mysql-proxy来进行配置
1
2
| [21:47 root@centos6.8~]# mysql-proxy --daemon -r 10.1.252.215:3306 -b 10.1.252.235:3306 --plugins="proxy" -s "/usr/share/oc/mysql-proxy-0.8.5/examples/rw-splitting.lua"
[21:47 root@centos6.8~]# 2016-11-05 21:47:10: (critical) plugin proxy 0.8.5 started
|
6、查看端口，mysql-proxy的4040端口已然处于监听状态
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| [21:47 root@centos6.8/usr/share/doc/mysql-proxy-0.8.5/examples]# ss -tanl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:42142 *:*
LISTEN 0 25 :::514 :::*
LISTEN 0 25 *:514 *:*
LISTEN 0 128 *:4040 *:*
LISTEN 0 128 :::111 :::*
LISTEN 0 128 *:111 *:*
LISTEN 0 128 :::58800 :::*
LISTEN 0 32 *:21 *:*
LISTEN 0 128 :::22 :::*
LISTEN 0 128 *:22 *:*
LISTEN 0 128 127.0.0.1:631 *:*
LISTEN 0 128 ::1:631 :::*
|
7、测试：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
| [iyunv@localhost ~]# mysql -utest1 -h10.1.252.109 -p --port=4040
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.73-log Source distribution
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> exit #成功登录mysql-proxy端
Bye
[iyunv@localhost ~]# mysql -utest1 -h10.1.252.109 -p --port=4040 -e "show databases;"
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mysqlreplicaton |
| nihao |
| test |
+--------------------+
[iyunv@localhost ~]# mysql -utest1 -h10.1.252.109 -p123456 --port=4040 -e "create database mysqlproxy;"
[iyunv@localhost ~]# mysql -utest1 -h10.1.252.109 -p --port=4040 -e "show databases;"
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mysqlproxy |
| mysqlreplicaton |
| nihao |
| test |
+--------------------+
|
当然，效果不是很明显，我们可以使用mysql-proxy提供的管理接口admin.lua来进行观察，其管理端口会监听在4041端口，只不过这个在安装时默认没有提供这个脚本，在网上找了个来试，提示脚本错误，所以没法进行测试。
|
|
QQ群⑧：
窥视卡
雷达卡
发表于 2016-11-7 09:34:36
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡