MySQL 主主复制

注意是在MySQL主从的基础上做MySQL主主。互为主从。

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 slave@'192.168.10.17' identified by '123456'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> select * from mysql.user where user='slave'\G; *************************** 1. row ***************************                   Host: 192.168.10.17                   User: slave            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 11:58:43      password_lifetime: NULL         account_locked: N 1 row in set (0.01 sec)   ERROR: No query specified   mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)

　　修改18上的配置文件并重启

?

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

[root@save-1 ~]# /etc/init.d/mysqld restart Shutting down MySQL.... SUCCESS! Starting MySQL.. SUCCESS! [root@save-1 ~]# cat /etc/my.cnf [mysqld] datadir=/usr/local/mysql/data socket=/usr/local/mysql/mysql.sock symbolic-links=0 server-id=2 log-bin=mysql-bin-slave #开启二进制日志 binlog-do-db=test #要复制的库 binlog-ignore-db=mysql #禁止同步的库    [mysqld_safe] log-error=/usr/local/mysql/logs/error.log pid-file=/usr/local/mysql/mysql.pid    [client] socket=/usr/local/mysql/mysql.sock

　　查看日志号

?

1 2 3 4 5 6 7

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

　　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

[root@master ~]# mysql -uslave -p123456 -h192.168.10.18 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.7.18-log MySQL Community Server (GPL)   Copyright (c) 2000, 2017, 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> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.01 sec)   mysql> quit Bye

　　开启主从

?

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, 1 warning (0.00 sec)   mysql> change master to master_host='192.168.10.18',master_port=3306,master_user='slave',master_password='123456',master_log_file='mysql-bin-slave.000001',master_log_pos=154; Query OK, 0 rows affected, 2 warnings (0.14 sec)   mysql> start slave; Query OK, 0 rows affected (0.01 sec)   mysql> show slave status\G *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.10.18                   Master_User: slave                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin-slave.000001           Read_Master_Log_Pos: 154                Relay_Log_File: master-relay-bin.000002                 Relay_Log_Pos: 326         Relay_Master_Log_File: mysql-bin-slave.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: 154               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: 38509892-7e06-11ea-87d4-000c29cbcf38              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)

　　查入数据测试

?

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

insert into test.emp values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20); mysql> insert into test.emp values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30); Query OK, 1 row affected (0.10 sec) mysql> select * from emp; +-------+-------+----------+------+---------------------+---------+--------+--------+ | empno | ename | job      | mgr  | hiredate            | sal     | comm   | deptno | +-------+-------+----------+------+---------------------+---------+--------+--------+ |  7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 |     30 | |  7521 | WARD  | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 |     30 | |  7566 | JONES | MANAGER  | 7839 | 1981-04-02 00:00:00 | 2975.00 |   NULL |     20 | +-------+-------+----------+------+---------------------+---------+--------+--------+ 4 rows in set (0.00 sec)