基本信息
mysql> show variables where Variable_name in ('log_error','general_log','general_log_file','slow_query_log','slow_query_log_file','datadir','basedir','innodb_buffer_pool_size','performance_schema','version','character_set_database');
+-------------------------+------------------------------------------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------------------------------------------+
| basedir | /usr/ |
| character_set_database | utf8mb4 |
| datadir | /var/lib/mysql/data/ |
| general_log | OFF |
| general_log_file | /var/lib/mysql/data/RDS-9A434B86-5696-923D-AF2E-2A9A1450B86F.log |
| innodb_buffer_pool_size | 9814671360 |
| log_error | /var/log/mysqld.log |
| performance_schema | ON |
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/data/mysql-slow.log |
| version | 5.6.35-log |
+-------------------------+------------------------------------------------------------------+
11 rows in set (0.01 sec)
//数据库
mysql> SELECT TABLE_SCHEMA,concat(round((sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024/1024,2),'MB') as size FROM information_schema.TABLES group by TABLE_SCHEMA;
+--------------------+------------+
| TABLE_SCHEMA | size |
+--------------------+------------+
| agiledb | 96948.18MB |
| information_schema | 0.01MB |
| mysql | 2.49MB |
| performance_schema | 0.00MB |
+--------------------+------------+
4 rows in set, 1 warning (0.05 sec)
创建库和授权
mysql> create database mycat_eye;
Query OK, 1 row affected (0.01 sec) --创建mycat_eye库
mysql> GRANT all ON mycat_eye.* TO root@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.03 sec)--给dss用户对mycat_eye库授权
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)--刷新系统权限表
显示表记录
mysql> select table_name,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2),'MB') as size,table_rows from information_schema.tables where table_schema="mycat_
eye" order by table_rows desc limit 10;
+----------------------+---------+------------+
| table_name | size | table_rows |
+----------------------+---------+------------+
| mysql_status_history | 18.06MB | 45362 |
| mysql_server | 0.03MB | 3 |
| mysql_cluster | 0.02MB | 1 |
| sys_user | 0.05MB | 1 |
| mysql_weak_password | 0.03MB | 1 |
+----------------------+---------+------------+
5 rows in set (0.00 sec)
二进制
mysql> SHOW BINARY LOGS;
+-----------+-----------+
| Log_name | File_size |
+-----------+-----------+
| ON.000001 | 87199642 |
+-----------+-----------+
1 row in set (0.00 sec)
mysql> SHOW BINLOG EVENTS IN 'ON.000001' LIMIT 10, 8;
+-----------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------+
| ON.000001 | 1286 | Anonymous_Gtid | 1122 | 1351 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| ON.000001 | 1351 | Query | 1122 | 1428 | BEGIN |
| ON.000001 | 1428 | Table_map | 1122 | 1494 | table_id: 108 (mycat_eye.mysql_cluster) |
| ON.000001 | 1494 | Write_rows | 1122 | 1556 | table_id: 108 flags: STMT_END_F |
| ON.000001 | 1556 | Xid | 1122 | 1587 | COMMIT /* xid=45 */ |
| ON.000001 | 1587 | Anonymous_Gtid | 1122 | 1652 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| ON.000001 | 1652 | Query | 1122 | 1797 | use `mycat_eye`; DROP TABLE IF EXISTS `mysql_server` /* generated by server */ |
| ON.000001 | 1797 | Anonymous_Gtid | 1122 | 1862 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
+-----------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
主Master信息
mysql> show master status;
+-----------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------+----------+--------------+------------------+-------------------+
| ON.000001 | 87581468 | | | |
+-----------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 1127 | | 3307 | 1122 | ec83bdff-d4d1-11e7-a203-00ff3dca5ad5 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)
从slave信息
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: ON.000001
Read_Master_Log_Pos: 87963280
Relay_Log_File: AHQ-PC-01057-relay-bin.000005
Relay_Log_Pos: 6680878
Relay_Master_Log_File: ON.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: 87963280
Relay_Log_Space: 87963503
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: 1122
Master_UUID: ec83bdff-d4d1-11e7-a203-00ff3dca5ad4
Master_Info_File: D:\Mysql\MySQL5.72\data\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)
主从配置
1、主从服务器分别作以下操作:
版本一致
2、修改主服务器master:
linux my.cnf
win my-default.ini
[mysqld]
log_bin = ON //[必须]启用二进制日志
binlog_format = ROW
server_id = 1122 //[必须]服务器唯一ID
3、修改从服务器slave:
#vi /etc/my.cnf
[mysqld]
log_bin = ON //[不是必须]启用二进制日志
server_id = 1123 //[必须]服务器唯一ID
执行
stop slave;
change master to master_host='127.0.0.1',master_user='root',master_password='123456',master_log_file='ON.000001',master_log_pos=463;
start slave;
解决 Slave_SQL_Running: No
stop slave;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave;
#免密码登录
my-default.ini的[mysqld]新增
#免密码登录
skip-grant-tables
#修改管理员密码
#update user set password=PASSWORD("123456") where user='root';
半同步
在半同步复制中, 为了保证主库上的每一个binlog事务都能够被可靠的复制到从库上,主库在每次事务成功提交时, 并不及时反馈给前端应用用户,而是等待其中之一个从库也接收到Binlog事务并成功写入中继日志后, 主库才返回Commit操作成功给客户端。如果同步出现故障, 则MySQL自动调整复制为异步模式,事务正常返回提交结果给客户端。半同步服务需要安装插件.
1. 首先判断MySQL服务器是否支持动态增加插件
mysql> select @@have_dynamic_loading;
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES |
+------------------------+
1 row in set (0.00 sec)
2 安装插件
#查看插件存入地址
mysql> show variables like 'plugin_dir';
+---------------+------------------------------------------+
| Variable_name | Value |
+---------------+------------------------------------------+
| plugin_dir | D:\mysql\mysql-5.6.25-winx64\lib\plugin\ |
+---------------+------------------------------------------+
1 row in set (0.00 sec)
#Master安装 semisync_master.dll(linux安装semisync_master.so)
mysql> install plugin rpl_semi_sync_master SONAME 'semisync_master.dll';
Query OK, 0 rows affected (0.07 sec)
#Slave安装(linux安装semisync_slave.so)
mysql> install plugin rpl_semi_sync_slave SONAME 'semisync_slave.dll';
Query OK, 0 rows affected (0.49 sec)
#查看Master
mysql> select * from mysql.plugin;
+----------------------+---------------------+
| name | dl |
+----------------------+---------------------+
| rpl_semi_sync_master | semisync_master.dll |
+----------------------+---------------------+
1 row in set (0.00 sec)
#查看Slave
mysql> select * from mysql.plugin;
+---------------------+--------------------+
| name | dl |
+---------------------+--------------------+
| rpl_semi_sync_slave | semisync_slave.dll |
+---------------------+--------------------+
1 row in set (0.10 sec)
3.分别配置主从数据库my-default.ini, 打开半同步semi-sync
Master(linux my.cnf)
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=30000
Slave(my.cnf)
rpl_semi_sync_slave_enabled=1
4.检查
Master
mysql> show status like '%semi_sync%';
+--------------------------------------------+-------+
| 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 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
mysql> show variables like '%Rpl%';
+------------------------------------+----------+
| Variable_name | Value |
+------------------------------------+----------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 30000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_stop_slave_timeout | 31536000 |
+------------------------------------+----------+
5 rows in set (0.00 sec)
Slave
mysql> show status like '%semi_sync%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+
1 row in set (0.02 sec)
mysql> show variables like '%Rpl%';
+---------------------------------+----------+
| Variable_name | Value |
+---------------------------------+----------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
| rpl_stop_slave_timeout | 31536000 |
+---------------------------------+----------+
3 rows in set (0.00 sec)