mysql主从复制 (二进制方式)
mysql-community-client.x86_64 0:5.7.17-1.el6 mysql-community-common.x86_64 0:5.7.17-1.el6 mysql-community-libs.x86_64 0:5.7.17-1.el6
mysql-community-libs-compat.x86_64 0:5.7.17-1.el6 mysql-community-server.x86_64 0:5.7.17-1.el6
/etc/init.d/mysqld start
cat /var/log/mysqld.log #找初始密码
ALTER USER root@localhost identified by 'Zeinima748!!'; #进入数据库修改密码
- master
修改/etc/my.cnf
最后添加
server-id=1
log-bin=mysql-bin #开启二进制
- slave
server-id=2
进入数据库
- master
1:创建共享用户
mysql> grant replication slave on *.* to dpq@'172.25.200.2' identified by 'Zeinima748!!';
Query OK, 0 rows affected, 1 warning (0.44 sec)
2:刷新
mysql> flush privileges;
Query OK, 0 rows affected (0.17 sec)
3:查看
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 599 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
4:创建 共享目录
mysql> create database westos;
Query OK, 1 row affected (0.13 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| westos |
+--------------------+
5 rows in set (0.00 sec)
#创建table;
mysql> use westos;
Database changed
mysql> create table usertb (
-> username varchar(20) not null,
-> password varchar(30) not null);
Query OK, 0 rows affected (1.41 sec)
mysql> desc usetb;
ERROR 1146 (42S02): Table 'westos.usetb' doesn't exist
mysql> desc usertb;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | NO | | NULL | |
| password | varchar(30) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
#插入数据
mysql> insert into usertb values ('user1','123');
Query OK, 1 row affected (0.31 sec)
- slave
连接
mysql> change master to master_host='172.25.200.1',master_user='dpq',master_password='Zeinima748!!',master_log_file='mysql-bin.000001',master_log_pos=599;
Query OK, 0 rows affected, 2 warnings (1.70 sec)
mysql> start slave;
Query OK, 0 rows affected (0.17 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.200.1
Master_User: dpq
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 599
Relay_Log_File: server2-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes #必须为yes
Slave_SQL_Running: Yes #必须为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: 599
Relay_Log_Space: 529
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
Master_UUID: db2b6e93-0498-11e8-9b4d-525400f2a3c0
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:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| westos |
+--------------------+
5 rows in set (0.00 sec)
mysql> use westos;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+------------------+
| Tables_in_westos |
+------------------+
| usertb |
+------------------+
1 row in set (0.00 sec)
mysql> desc usertb;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | NO | | NULL | |
| password | varchar(30) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
+----------+----------+
1 row in set (0.00 sec)
主从复制 (gtid 方式) (下一个下一个方式)
- master && slave
/etc/my.cnf
gtid_mode=ON
enforce-gtid-consistency=true
- slave
change master to master_host='172.25.200.1',master_user='dpq',master_passsword='Zeinima748!!',master_auto_position=1;
mysql并行复制
- slave
/etc/my.cnf
31 server-id=2
32 #log-bin=mysql-bin
33 gtid_mode=ON
34 enforce-gtid-consistency=true
37 slave-parallel-type=LOGICAL_CLOCK
38 slave-parallel-workers=16
39 master_info_repository=TABLE
40 relay_log_info_repository=TABLE
41 relay_log_recovery=ON
mysql> show processlist;
半同步复制
- master
- 1:下载半同步插件
install plugin rpl_semi_sync_master soname 'semisync_master.so';
- 2:查看最后一行是否下载成功
show plugins;
show variables like '%rpl%';
set global rpl_semi_sync_master_enabled =1;
show variables like '%rpl%'
show status like 'Rpl_semi_sync%'; #查看最后一行是否为提交成功
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 1 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 4 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 988 |
| Rpl_semi_sync_master_tx_wait_time | 988 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
- slave
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
show plugins;
show variables like '%rpl%';
set global rpl_semi_sync_slave_enabled =1;
show variables like '%rpl%';
stop slave io_thread;
start slave io_thread;
读写分离
- server1 server2 配置好主从复制
- server3
tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/
mv mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy
/usr/local/mysql-proxy/share/doc/mysql-proxy
40 min_idle_connections = 1,
41 max_idle_connections = 2,
[root@server3 mysql-proxy]# mkdir etc
[root@server3 mysql-proxy]# mkdir logs
[root@server3 ~]# cat /usr/local/mysql-proxy/etc/mysql-proxy.conf
[mysql-proxy]
proxy-address=172.25.200.3:3306
proxy-read-only-backend-addresses=172.25.200.2:3306
proxy-backend-addresses=172.25.200.1:3306
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
daemon=true
user=root
log-level=debug
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log
keepalive=true
admin-username=admin
admin-password=westos
admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua
[root@server3 ~]# netstat -antlp
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 914/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 990/master
tcp 0 0 0.0.0.0:4041 ! 0.0.0.0:* LISTEN 1231/mysql-proxy
tcp 0 0 172.25.200.3:3306 ! 0.0.0.0:* LISTEN 1231/mysql-proxy
tcp 0 0 172.25.200.3:22 172.25.200.250:46910 ESTABLISHED 1038/sshd
tcp 0 0 172.25.200.3:22 172.25.200.250:46914 ESTABLISHED 1099/sshd
tcp 0 0 :::22 :::* LISTEN 914/sshd
tcp 0 0 ::1:25 :::* LISTEN 990/master
- 调度器
[root@server3 ~]# mysql -uadmin -pwestos -h 172.25.200.3 -P4041 #查看调度器状态
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from backends;
+-------------+-------------------+-------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+-------------------+-------+------+------+-------------------+
| 1 | 172.25.200.1:3306 | up | rw | NULL | 0 |
| 2 | 172.25.200.2:3306 | up | ro | NULL | 0 |
+-------------+-------------------+-------+------+------+-------------------+
2 rows in set (0.00 sec)
- 真机
[root@foundation1 day07]# mysql -uproxy -h 172.25.200.3 -p #通过调度器查看内容