Mysql 主从从

数据准备（三台机器都要）

?

1 2 3 4 5 6 7 8 9 10

mysql>  create database test; Query OK, 1 row affected (0.00 sec)   mysql>  use test;   mysql> create table emp (empno numeric(4) not null,ename varchar(10),job varchar(9),mgr numeric(4),hiredate datetime,sal numeric(7, 2),comm numeric(7, 2),deptno numeric(2)); Query OK, 0 rows affected (0.04 sec)   mysql> insert into emp values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20); Query OK, 1 row affected (0.04 sec)

　　192.168.10.16作为主创建用户192.168.10.17机器连接用户刷新授权表

?

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 44 45 46 47 48 49 50 51 52 53 54

mysql>  select * from mysql.user where user='repl'\G; *************************** 1. row ***************************                   Host: 192.168.10.17                   User: repl            Select_priv: N            Insert_priv: N            Update_priv: N            Delete_priv: N            Create_priv: N              Drop_priv: N            Reload_priv: N          Shutdown_priv: N           Process_priv: N              File_priv: N             Grant_priv: N        References_priv: N             Index_priv: N             Alter_priv: N           Show_db_priv: N             Super_priv: N  Create_tmp_table_priv: N       Lock_tables_priv: N           Execute_priv: N        Repl_slave_priv: Y       Repl_client_priv: N       Create_view_priv: N         Show_view_priv: N    Create_routine_priv: N     Alter_routine_priv: N       Create_user_priv: N             Event_priv: N           Trigger_priv: N Create_tablespace_priv: N               ssl_type:             ssl_cipher:            x509_issuer:           x509_subject:          max_questions: 0            max_updates: 0        max_connections: 0   max_user_connections: 0                 plugin: mysql_native_password  authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9       password_expired: N  password_last_changed: 2020-04-15 15:37:09      password_lifetime: NULL         account_locked: N 1 row in set (0.00 sec)   ERROR: No query specified   mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)

　　修改192.168.10.16的配置文件

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

[root@master ~]# cat /etc/my.cnf [mysqld] datadir=/usr/local/mysql/data socket=/usr/local/mysql/mysql.sock symbolic-links=0 server-id=1 binlog-do-db=test log-bin=mysql-bin-master binlog-ignore-db=mysql sync-binlog=1 #启用二进制日志同步 binlog-format=row #启用二进制日志格式化，以行的方式格式化   [mysqld_safe] log-error=/usr/local/mysql/logs/error.log pid-file=/usr/local/mysql/mysql.pid   [client] socket=/usr/local/mysql/mysql.sock [root@master ~]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL.... SUCCESS!

　　登录192.168.10.17机器创建授权用户给192.168.10.18

?

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 44 45 46 47 48 49 50 51 52 53 54 55 56 57

mysql>  grant replication slave on *.* to repl@'192.168.10.18' identified by '123456'; Query OK, 0 rows affected, 1 warning (0.03 sec)   mysql>  select * from mysql.user where user='repl'\G; *************************** 1. row ***************************                   Host: 192.168.10.18                   User: repl            Select_priv: N            Insert_priv: N            Update_priv: N            Delete_priv: N            Create_priv: N              Drop_priv: N            Reload_priv: N          Shutdown_priv: N           Process_priv: N              File_priv: N             Grant_priv: N        References_priv: N             Index_priv: N             Alter_priv: N           Show_db_priv: N             Super_priv: N  Create_tmp_table_priv: N       Lock_tables_priv: N           Execute_priv: N        Repl_slave_priv: Y       Repl_client_priv: N       Create_view_priv: N         Show_view_priv: N    Create_routine_priv: N     Alter_routine_priv: N       Create_user_priv: N             Event_priv: N           Trigger_priv: N Create_tablespace_priv: N               ssl_type:             ssl_cipher:            x509_issuer:           x509_subject:          max_questions: 0            max_updates: 0        max_connections: 0   max_user_connections: 0                 plugin: mysql_native_password  authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9       password_expired: N  password_last_changed: 2020-04-15 15:50:12      password_lifetime: NULL         account_locked: N 1 row in set (0.03 sec)   ERROR: No query specified   mysql> flush privileges; Query OK, 0 rows affected (0.03 sec)

　　修改192.168.10.17上配置文件并重启

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

#vim /etc/my.cnf   [mysqld] datadir=/usr/local/mysql/data socket=/usr/local/mysql/mysql.sock symbolic-links=0 log-bin=mysql-bin-relay server-id=2 log-slave-updates=1 #把它从relay-log当中读取出来的二进制日志并且在本机上执行的操作也记录自己的二进制日志里面，这样才能使第三台A76通过中继A75slave读取到相应数据变化 binlog-format=row #启用二进制日志格式化，以行的方式格式化 [mysqld_safe] log-error=/usr/local/mysql/logs/error.log pid-file=/usr/local/mysql/mysql.pid   [client] socket=/usr/local/mysql/mysql.sock [root@master ~]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL.. SUCCESS!

　查看192.168.10.16的二进制日志

?

1 2 3 4 5 6 7

mysql>  show master status; +-------------------------+----------+--------------+------------------+-------------------+ | File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------------+----------+--------------+------------------+-------------------+ | mysql-bin-master.000001 |      615 | test         | mysql            |                   | +-------------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)

　　配置192.168.10.17的主。抓取二进制日志去找192.168.10.16；

?

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 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130

mysql> stop slave; Query OK, 0 rows affected (0.04 sec)   mysql> change master to master_host='192.168.10.16',master_user='repl',master_password='123456',master_log_file='mysql-bin-master.000001',master_log_pos=615; Query OK, 0 rows affected, 2 warnings (0.03 sec)   mysql> show slave status\G *************************** 1. row ***************************                Slave_IO_State:                   Master_Host: 192.168.10.16                   Master_User: repl                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin-master.000001           Read_Master_Log_Pos: 615                Relay_Log_File: master-relay-bin.000001                 Relay_Log_Pos: 4         Relay_Master_Log_File: mysql-bin-master.000001              Slave_IO_Running: No             Slave_SQL_Running: No               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: 615               Relay_Log_Space: 154               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: 1130                 Last_IO_Error: error connecting to master 'slave@192.168.10.18:3306' - retry-time: 60  retries: 29                Last_SQL_Errno: 0                Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 0                   Master_UUID:              Master_Info_File: /usr/local/mysql/data/master.info                     SQL_Delay: 0           SQL_Remaining_Delay: NULL       Slave_SQL_Running_State:            Master_Retry_Count: 86400                   Master_Bind:       Last_IO_Error_Timestamp: 200415 16:26:28      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)   mysql> start slave; Query OK, 0 rows affected (0.02 sec)   mysql> show slave status\G *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.10.16                   Master_User: repl                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin-master.000001           Read_Master_Log_Pos: 615                Relay_Log_File: master-relay-bin.000002                 Relay_Log_Pos: 327         Relay_Master_Log_File: mysql-bin-master.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: 615               Relay_Log_Space: 535               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: 615a0cd8-5e0c-11ea-95be-000c29168707              Master_Info_File: /usr/local/mysql/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)

　　在192.168.10.17里给192.168.10.18创建用户

?

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 44 45 46 47 48 49 50 51 52 53 54 55 56

mysql>  grant replication slave on *.* to repl@'192.168.10.18' identified by '123456'; Query OK, 0 rows affected, 1 warning (0.01 sec)   mysql> select * from mysql.user where user='repl'\G; *************************** 1. row ***************************                   Host: 192.168.10.18                   User: repl            Select_priv: N            Insert_priv: N            Update_priv: N            Delete_priv: N            Create_priv: N              Drop_priv: N            Reload_priv: N          Shutdown_priv: N           Process_priv: N              File_priv: N             Grant_priv: N        References_priv: N             Index_priv: N             Alter_priv: N           Show_db_priv: N             Super_priv: N  Create_tmp_table_priv: N       Lock_tables_priv: N           Execute_priv: N        Repl_slave_priv: Y       Repl_client_priv: N       Create_view_priv: N         Show_view_priv: N    Create_routine_priv: N     Alter_routine_priv: N       Create_user_priv: N             Event_priv: N           Trigger_priv: N Create_tablespace_priv: N               ssl_type:             ssl_cipher:            x509_issuer:           x509_subject:          max_questions: 0            max_updates: 0        max_connections: 0   max_user_connections: 0                 plugin: mysql_native_password  authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9       password_expired: N  password_last_changed: 2020-04-15 16:30:45      password_lifetime: NULL         account_locked: N 1 row in set (0.00 sec)   ERROR: No query specified mysql>  flush privileges; Query OK, 0 rows affected (0.01 sec)

　　配置文件因为上面改好了；修改192.168.10.18配置文件并重启

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

[root@save-1 ~]# cat /etc/my.cnf [mysqld] datadir=/usr/local/mysql/data socket=/usr/local/mysql/mysql.sock symbolic-links=0 log-bin=mysql-bin-slave1 #启用二进制日志 server-id=3 #id binlog-format=row #启用二进制日志格式化，以行的方式格式化 [mysqld_safe] log-error=/usr/local/mysql/logs/error.log pid-file=/usr/local/mysql/mysql.pid    [client] socket=/usr/local/mysql/mysql.sock [root@save-1 ~]# /etc/init.d/mysqld restart

　　查看192.168.10.17 的二进制日志序列号

?

1 2 3 4 5 6 7

mysql> show master status; +------------------------+----------+--------------+------------------+-------------------+ | File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------------+----------+--------------+------------------+-------------------+ | mysql-bin-relay.000001 |      601 |              |                  |                   | +------------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.01 sec)

　　191.168.10.18配置抓取二进制日志去找192.168.10.17

?

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 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69

mysql>  stop slave; Query OK, 0 rows affected (0.03 sec)   mysql> change master to master_host='192.168.10.17',master_user='repl',master_password='123456',master_log_file='mysql-bin-relay.000001',master_log_pos=601; Query OK, 0 rows affected, 2 warnings (0.03 sec)   mysql> start slave; Query OK, 0 rows affected (0.04 sec)   mysql> show slave status \G *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.10.17                   Master_User: repl                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin-relay.000001           Read_Master_Log_Pos: 601                Relay_Log_File: save-1-relay-bin.000002                 Relay_Log_Pos: 326         Relay_Master_Log_File: mysql-bin-relay.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: 601               Relay_Log_Space: 534               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: 2                   Master_UUID: 88976e86-7d66-11ea-967b-000c29f1686c              Master_Info_File: /usr/local/mysql/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)

　　

在16主上插入数据。（三台一起查看test库emp表是否同步)

?

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql>  insert into test.emp values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30); Query OK, 1 row affected (0.38 sec)   mysql> select * from emp; ERROR 1046 (3D000): No database selected mysql> select * from test.emp; +-------+-------+----------+------+---------------------+---------+--------+--------+ | empno | ename | job      | mgr  | hiredate            | sal     | comm   | deptno | +-------+-------+----------+------+---------------------+---------+--------+--------+ |  7369 | SMITH | CLERK    | 7902 | 1980-12-17 00:00:00 |  800.00 |   NULL |     20 | |  7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 |     30 | +-------+-------+----------+------+---------------------+---------+--------+--------+ 2 rows in set (0.00 sec)

　　

下面开始把17做为中继服务器：假设中继不需要存储数据，只需要做一些分发的二进制日志的一个桥梁，可以修改成黑洞引擎。

在修改黑洞引擎之前需要先把二进制日志同步功能关掉；避免A76从数据库会来A75中继数据库抓取二进制日志，如果没有停止：那样就等于A75中继有什么操作A76数据库就有什么操作。

查看17中继的test库emp表存储引擎

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

mysql> show create table test.emp \G; *************************** 1. row ***************************        Table: emp Create Table: CREATE TABLE `emp` (   `empno` decimal(4,0) NOT NULL,   `ename` varchar(10) DEFAULT NULL,   `job` varchar(9) DEFAULT NULL,   `mgr` decimal(4,0) DEFAULT NULL,   `hiredate` datetime DEFAULT NULL,   `sal` decimal(7,2) DEFAULT NULL,   `comm` decimal(7,2) DEFAULT NULL,   `deptno` decimal(2,0) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)   ERROR: No query specified

　　查看MySQL所有存储引擎；默认InnoDB

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> SHOW ENGINES; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         | | MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         | | MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         | | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         | | InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        | | PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         | | ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         | | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         | | FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

　　临时关闭二进制日志同步

?

1 2	mysql> set sql_log_bin=off;   #永久关闭配置文件default-storage-engine=blackhole Query OK, 0 rows affected (0.00 sec)

　　把test库emp表修改成黑洞引擎，(作为中继，不存储数据。只作为分发二进制日志使用)

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

mysql> alter table test.emp engine=blackhole; Query OK, 1 row affected (0.04 sec) Records: 1  Duplicates: 0  Warnings: 0   mysql> show create table test.emp \G;  查看 *************************** 1. row ***************************        Table: emp Create Table: CREATE TABLE `emp` (   `empno` decimal(4,0) NOT NULL,   `ename` varchar(10) DEFAULT NULL,   `job` varchar(9) DEFAULT NULL,   `mgr` decimal(4,0) DEFAULT NULL,   `hiredate` datetime DEFAULT NULL,   `sal` decimal(7,2) DEFAULT NULL,   `comm` decimal(7,2) DEFAULT NULL,   `deptno` decimal(2,0) DEFAULT NULL ) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 1 row in set (0.03 sec)   ERROR: No query specified

　最后打开二进制日志

?

1 2	mysql> set sql_log_bin=on; Query OK, 0 rows affected (0.01 sec)

　　插入数据测试

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

mysql> insert into test.emp values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20); Query OK, 1 row affected (0.02 sec)   mysql> select * from test.emp;    192.168.10.16 +-------+-------+----------+------+---------------------+---------+--------+--------+ | empno | ename | job      | mgr  | hiredate            | sal     | comm   | deptno | +-------+-------+----------+------+---------------------+---------+--------+--------+ |  7369 | SMITH | CLERK    | 7902 | 1980-12-17 00:00:00 |  800.00 |   NULL |     20 | |  7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 |     30 | |  7566 | JONES | MANAGER  | 7839 | 1981-04-02 00:00:00 | 2975.00 |   NULL |     20 | +-------+-------+----------+------+---------------------+---------+--------+--------+ 3 rows in set (0.00 sec) mysql> select * from test.emp;   192.168.10.17作为中继不写数据；只分发二进制日志 Empty set (0.00 sec) mysql> select * from test.emp;   192.168.10.18 +-------+-------+----------+------+---------------------+---------+--------+--------+ | empno | ename | job      | mgr  | hiredate            | sal     | comm   | deptno | +-------+-------+----------+------+---------------------+---------+--------+--------+ |  7369 | SMITH | CLERK    | 7902 | 1980-12-17 00:00:00 |  800.00 |   NULL |     20 | |  7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 |     30 | |  7566 | JONES | MANAGER  | 7839 | 1981-04-02 00:00:00 | 2975.00 |   NULL |     20 | +-------+-------+----------+------+---------------------+---------+--------+--------+ 3 rows in set (0.00 sec)