首页 > 代码库 > mysql主从架构
mysql主从架构
############################################################
##############mysql主从架构#################
Server11.
yum install mysql-server -y
vim /etc/my.cnf
log-bin=mysql-bin #启动二进制日志系统
binlog-do-db=test #二进制需要同步的数据库名,如果需要同步多个库,例如要再同步 westos
库,再添加一行“binlog-do-db=westos”,以此类推
server-id=3
#必须为 1 到 232–1 之间的一个正整数值
binlog-ignore-db=mysql #禁止同步 mysql 数据库
/etc/init.d/mysqld start
Mysql
查看和授权
Server12
yum install mysql-server -y
vim /etc/my.cnf添加两行
vsymbolic-links=0
server-id=4
验证授权之后是否可以登陆
/etc/init.d/mysqld start
Mysql
出现说明成功了
测试
Server11
mysql
登陆server12上的mysql
##############################################################
###############mysql的gtid机制#############
Server11.和server12相同
/etc/init.d/mysqld stop
yum remove mysql-server -y
rm -fr /var/lib/mysql/*
tar xf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
yum install -y mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm
/etc/init.d/mysqld start
cat /var/log/mysqld.log | grep password 初始化密码在这
mysql -p
退出并且关掉mysql
Server11
vim /etc/my.cnf
Server12
vim /etc/my.cnf
两个服务器都打开mysql
Server11
授权
Server12
查看是否授权成功
添加主服务器的相关信息
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.45.11
Master_User: student ####主服务起的IP、用户名和端口信息
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 450
Relay_Log_File: server12-relay-bin.000002
Relay_Log_Pos: 663
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes IO和SQL线程开启
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: 450
Relay_Log_Space: 873
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: 3
Master_UUID: ae5b62c7-1548-11e7-a52c-525400cc54de
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: ae5b62c7-1548-11e7-a52c-525400cc54de:1
Executed_Gtid_Set: ae5b62c7-1548-11e7-a52c-525400cc54de:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
查看 /var/lib/mysql/mysqlbinlog mysql-bin.000003
#170330 8:41:02 server id 4 end_log_pos 194 CRC32 0xeb5aadd3 Previous-GTIDs
# ae5b62c7-1548-11e7-a52c-525400cc54de:1-5
两个gtid应该一样
Sever11
创建库并且存入相关信息
Server12查看
主从复制成功
##########mysql主从的半同步和异步传输日志文件数据##########
Mysql版本必须时5.5以上
接下来分别在主库和从库执行install plugin 命令加载插件，并且开启半同步插件，查看插件的安装情况
主库
从库
安装情况
Rpl_semi_sync_master_timeout表示主库等待从库的超时时间，如果在时间内没有收到从库回应，复制切换到异步模式
在从库上停止io线程，在此时主库内同步数据，在10秒内自动切换到异步同步数据
然而下次继续同步数据时，便不用在等待，直接为异步同步，若打开从库的io线程，主库又恢复半同步复制数据
在mysql启动前将全局变量写入到配置文件中，以免忘记设置使半同步不生效
主库配置文件
[mysqld]
rpl_semi_sync_master_enabled=1
rpl__semi_sync_master_timeout=10000 ###10秒
从库配置文件
[mysqld]
rpl_semi_sync_slave_enabled=1
主库:
mysql> show global variables like ‘%rpl_semi%‘;
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.01 sec)
##IO线程关闭后，半同步状态检测的延时（10000ms）
mysql> show global status like ‘%rpl_semi%‘;
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
主库
mysql> show global variables like ‘%rpl_semi%‘;
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+-------------------------------------------+------------+
8 rows in set (0.01 sec)
mysql> show global status like ‘%rpl_semi%‘;
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
| Rpl_semi_sync_slave_status | OFF |
+--------------------------------------------+-------+
############sql线程的并行（多）线程##############
IO线程用于同步日志二进制文件数据
sql线程（原本为单线程）用于回放日志，将二进制文件的在slave上进行再次操作。sql线程在slave上
vim /etc/my.cnf##添加
15 slave-parallel-type=LOGICAL_CLOCK
16 slave-parallel-workers=16
17 master_info_repository=TABLE
18 relay_log_info_repository=TABLE
19 relay_log_recovery=ON
/etc/init.d/mysqld restart
mysql -p
Msyql-proxy读写分离
1.mysql proxy
tar -zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy
cd /usr/local/mysql-proxy
mkdir lua
Mkdir logs
cp /share/doc/mysql-proxy/rw-splitting.lua ./lua
cp /share/doc/mysql-proxy/admin-sql.lua ./lua
vim /usr/local/mysql-proxy/bin/mysql-proxy.conf
[mysql-proxy]
user=root
admin-username=proxy
admin-password=Westos+007
proxy-address=172.25.45.13:4040
proxy-read-only-backend-addresses=172.25.45.12
proxy-backend-addresses=172.25.45.11
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua
#admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log
log-level=info
daemon=true
keepalive=true
chmod 660 /usr/local/mysql-proxy/bin/mysql-proxy.conf
vim /usr/local/mysql-proxy/lua/rw-splitting.lua
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 1,
max_idle_connections = 1,
is_debug = false
改为1，方便测试
启动MYSQL-PROXY
/usr/lo/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/bin/mysql-proxy.conf
netstat -tupln | grep 4040
测试读写分离
主服务器
grant all on *.* to proxy@‘172.25.45.13‘ identified by ‘Westos+007‘;
使用客户端连接mysql-proxy
mysql -u proxy -h 172.25.45.13 -P 4041 -pWestos+007
################MHA###############3
在做好mysql的gtid机制和半同步的基础上
Server1：172.25.45.11 master
Server2：172.25.45.12slave（主从）
Server4：172.25.45.13 slave 从
Server5：172.25.45.14 manager
1.配置三台机子ssh无密码连接：
ssh-keygen ##每台机子都要生成自己的公秘钥对
ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.45.12
ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.45.13
ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.45.14
每台机子都要做上述操作
2.安装mha
所有节点都要安装
yum install -y perl-DBD-MySQL
rpm -ivh mha4mysql-node-0.54-0.el6.noarch.rpm
管理节点
yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager
rpm -ivh mha4mysql-manager-0.55-0.el6.noarch.rpm
配置mysql的主从replication：
主从服务器都需要有 vim /etc/my.cnf
binlog-do-db=test
replication-do-db=test
其中在从服务器的配置还需要多添加一行：
Relay_log_purge=0
一般情况下，主服务器需要包含binlog-do-db=test，从服务器需要包含replicate-do-db=test，这样主从就可以同步了。但是只是这样配置的话，会报以下错误
All log-bin enabled servers must have same binlog filtering rules (same binlog-do-db and binlog-ignore-db). Check SHOW MASTER STATUS output and set my.cnf correctly
3.配置mha manage
1，添加管理账号，每台机器都执行以下操作
grant all privileges on *.* to lzt@‘172.25.45.%‘ identified by ‘Westos+007‘;
flush privileges;
2，配置/etc/mha/app1.cnf，只在管理端做，manage这台机器
mkdir /etc/mha
mkdir -p /var/log/mha/app1
vim /etc/mha/app1.cnf ##配置如下:
[server default]
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1.log
master_binlog_dir=/var/lib/mysql
user=proxy
Password=Westos+007
ping_interval=2
repl_password=Westos+007
repl_user=lzt
ssh_user=root
[server1]
hostname=172.25.45.11
port=3306
[server2]
candidate_master=1
check_repl_delay=0
hostname=172.25.45.12
port=3306
[server3]
check_repl_delay=0
hostname=172.25.30.45.12
port=3306
4.检测mha是否配置成功：
A.检查ssh登陆：
masterha_check_ssh --conf=/etc/mha/app1.cnf
B.检测mha manage的监控状况：
3,管理端常用命令
masterha_check_ssh 检查MHA的SSH配置状况
masterha_check_repl 检查MySQL复制状况
masterha_manger 启动MHA
masterha_check_status 检测当前MHA运行状态
masterha_master_monitor 检测master是否宕机
masterha_master_switch 控制故障转移（自动或者手动）
masterha_conf_host 添加或删除配置的server信息
4.切换：
关闭master ，切换至主备
mysql主从架构