本文共 52383 字，大约阅读时间需要 174 分钟。
mysql5.7.28+centos7.6互为主从配置
基础环境信息：
|
| 主库 | 备库 |
| 主机名 | rac1 | rac2 |
| public ip | 192.168.52.150 | 192.168.52.151 |
| 数据库名 | master | |
| 操作系统用户 | root/1234 mysql/1234 | |
| 数据库用户 | 同步的用户：backup/123456 | |
| mysql版本 | mysql5.7.28 | |
| 操作系统 | centos7.6x64 最小化安装 | |
| 最低配置建议 | cpu 2c mem 4g swap 2g / 40G
| |
| /etc/profile | 添加路径：export PATH=/mysql/bin:$PATH | |
| /etc/my.cnf | # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html [client] socket=/mysql/mysql.sock default-character-set=utf8mb4 [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M basedir=/mysql datadir=/mysql/data socket=/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0
log-error=/mysql/logs/mysqld.log pid-file=/mysql/mysqld.pid explicit_defaults_for_timestamp=true ### server-id=134 log-bin=mysql-bin binlog-do-db=master binlog-ignore-db=mysql binlog-ignore-db=information_schema binlog-ignore-db=performance_schema binlog-ignore-db=sys expire_logs_days=7 character-set-server=utf8mb4 collation-server=utf8mb4_general_ci [mysql] default-character-set=utf8mb4 | |
将Mysql服务器的字符集设置为utf8mb4。通过命令 show variables like 'character%'; 可以查看数据库的字符集设置，下图是我已经配置好的截图。没配置之前，标红的两处变量character_set_database和character_set_server默认值都是latin1，必须都修改为 utf8mb4，否则其他修改后的字符集设置不会生效。
我这边是centos 7的云服务器， vim /etc/my.cnf 修改Mysql的配置文件，下面是正确的字符集配置
[client]default-character-set=utf8mb4[mysql]default-character-set=utf8mb4[mysqld]character-set-server=utf8mb4collation-server=utf8mb4_general_ci
主库:
[root@rac1 mysql]# groupadd mysql
[root@rac1 mysql]# useradd -r -g mysql mysql
[root@rac1 mysql]# mkdir -p /mysql/logs
[root@rac1 mysql]# mkdir -p /mysql/data
[root@rac1 mysql]# chown -R mysql:mysql /mysql
[root@rac1 mysql]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[client]
socket=/mysql/mysql.sock
default-character-set=utf8mb4
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
basedir=/mysql
datadir=/mysql/data
socket=/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/mysql/logs/mysqld.log
pid-file=/mysql/mysqld.pid
explicit_defaults_for_timestamp=true
###
server-id=134
log-bin=mysql-bin
binlog-do-db=master
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
expire_logs_days=7
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
[mysql]
default-character-set=utf8mb4
初始化mysql
[root@rac1 data]# mysqld --initialize --user=mysql --basedir=/mysql --datadir=/mysql/data
[root@rac1 data]# ls
auto.cnf ca.pem client-key.pem ibdata1 ib_logfile1 mysql-bin.000001 performance_schema public_key.pem server-key.pem
ca-key.pem client-cert.pem ib_buffer_pool ib_logfile0 mysql mysql-bin.index private_key.pem server-cert.pem sys
[root@rac1 data]# ls -l
total 110668
-rw-r----- 1 mysql mysql 56 Mar 10 16:21 auto.cnf
-rw------- 1 mysql mysql 1680 Mar 10 16:21 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 Mar 10 16:21 ca.pem
-rw-r--r-- 1 mysql mysql 1112 Mar 10 16:21 client-cert.pem
-rw------- 1 mysql mysql 1680 Mar 10 16:21 client-key.pem
-rw-r----- 1 mysql mysql 425 Mar 10 16:21 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Mar 10 16:21 ibdata1
-rw-r----- 1 mysql mysql 50331648 Mar 10 16:21 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Mar 10 16:21 ib_logfile1
drwxr-x--- 2 mysql mysql 4096 Mar 10 16:21 mysql
-rw-r----- 1 mysql mysql 177 Mar 10 16:21 mysql-bin.000001
-rw-r----- 1 mysql mysql 19 Mar 10 16:21 mysql-bin.index
drwxr-x--- 2 mysql mysql 8192 Mar 10 16:21 performance_schema
-rw------- 1 mysql mysql 1680 Mar 10 16:21 private_key.pem
-rw-r--r-- 1 mysql mysql 452 Mar 10 16:21 public_key.pem
-rw-r--r-- 1 mysql mysql 1112 Mar 10 16:21 server-cert.pem
-rw------- 1 mysql mysql 1676 Mar 10 16:21 server-key.pem
drwxr-x--- 2 mysql mysql 8192 Mar 10 16:21 sys
[root@rac1 data]# cd ../logs
[root@rac1 logs]# ls
mysqld.log
[root@rac1 logs]# more mysqld.log
2020-03-10T08:20:14.154018Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-03-10T08:20:14.182240Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-03-10T08:20:14.238148Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: f7a9c95b-62a7-11ea-8286-000c2964ad50.
2020-03-10T08:20:14.239025Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-03-10T08:20:14.601178Z 0 [Warning] CA certificate ca.pem is self signed.
2020-03-10T08:20:14.733085Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2020-03-10T08:21:25.047045Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-03-10T08:21:25.073196Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-03-10T08:21:25.131155Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 21eb3371-62a8-11ea-a7f7-000c2964ad50.
2020-03-10T08:21:25.132035Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-03-10T08:21:25.531788Z 0 [Warning] CA certificate ca.pem is self signed.
2020-03-10T08:21:25.778908Z 1 [Note] A temporary password is generated for root@localhost: xshVWOhOm0_:
配置mysql服务
[root@rac1 mysql]# cp -a /mysql/support-files/mysql.server /etc/init.d/mysqld
cp: overwrite 鈥etc/init.d/mysqld鈥 y
[root@rac1 mysql]# chkconfig mysqld on
[root@rac1 mysql]# chkconfig --list|grep mysqld
Note: This output shows SysV services only and does not include native
systemd services. SysV configuration data might be overridden by native
systemd configuration.
If you want to list systemd services use 'systemctl list-unit-files'.
To see services enabled on particular target use
'systemctl list-dependencies [target]'.
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@rac1 mysql]# systemctl start mysqld
[root@rac1 mysql]# systemctl status mysqld
鈼[0m mysqld.service - LSB: start and stop MySQL
Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
Active: active (running) since Tue 2020-03-10 16:33:58 CST; 6s ago
Docs: man:systemd-sysv-generator(8)
Process: 83750 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
CGroup: /system.slice/mysqld.service
鈹溾攢83767 /bin/sh /mysql/bin/mysqld_safe --datadir=/mysql/data --pid-file=/mysql/mysqld.pid
鈹斺攢84075 /mysql/bin/mysqld --basedir=/mysql --datadir=/mysql/data --plugin-dir=/mysql/lib/plugin --user=mysql --log-error=/mysql/logs/mysqld.log --pid-file=/mysql/mysqld.pid --socket=/mysql/mysql.sock
Mar 10 16:33:57 rac1 systemd[1]: Starting LSB: start and stop MySQL...
Mar 10 16:33:58 rac1 mysqld[83750]: Starting MySQL. SUCCESS!
Mar 10 16:33:58 rac1 systemd[1]: Started LSB: start and stop MySQL.
[root@rac1 mysql]#
修改root密码
[root@rac1 mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.28-log
Copyright (c) 2000, 2019, 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;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> set password = password('1234');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> use mysql
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> select host,user from user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
3 rows in set (0.00 sec)
mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select host,user from user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| % | root |
| localhost | mysql.session |
| localhost | mysql.sys |
+-----------+---------------+
3 rows in set (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO "root"@"%" IDENTIFIED BY '1234' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select host,user from user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| % | root |
| localhost | mysql.session |
| localhost | mysql.sys |
+-----------+---------------+
3 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@rac1 mysql]# mysql -u root -h 192.168.52.150 -p1234
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 4
Server version: 5.7.28-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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 |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> exit
Bye
[root@rac1 mysql]#
配置同步用户
[root@rac1 mysql]# mysql -u root -h 192.168.52.150 -p1234
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 5
Server version: 5.7.28-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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 variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| log_bin | ON |
| log_bin_basename | /mysql/data/mysql-bin |
| log_bin_index | /mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-----------------------------+
6 rows in set (0.00 sec)
mysql> grant replication slave on *.* to 'backup'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> use mysql
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> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user | authentication_string | host |
+---------------+-------------------------------------------+-----------+
| root | *A4B6157319038724E3560894F7F932C8886EBFCF | % |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| backup | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | % |
+---------------+-------------------------------------------+-----------+
4 rows in set (0.00 sec)
mysql> show master status\G #
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 675
Binlog_Do_DB: master
Binlog_Ignore_DB: mysql,information_schema,performance_schema,sys
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> show master status\G #
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 675
Binlog_Do_DB: master
Binlog_Ignore_DB: mysql,information_schema,performance_schema,sys
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> CREATE DATABASE master;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE `order_0` (
-> `order_id` int(11) NOT NULL,
-> `user_id` int(11) NOT NULL,
-> PRIMARY KEY (`order_id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| master |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> user master;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'user master' at line 1
mysql> use master
Database changed
mysql> CREATE TABLE `order_0` (
`order_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)
mysql> use mysql
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> help show
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:
SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW SLAVE HOSTS
SHOW SLAVE STATUS [FOR CHANNEL channel]
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]
like_or_where:
LIKE 'pattern'
| WHERE expr
If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL % and _ wildcard
characters. The pattern is useful for restricting statement output to
matching values.
Several SHOW statements also accept a WHERE clause that provides more
flexibility in specifying which rows to display. See
https://dev.mysql.com/doc/refman/5.7/en/extended-show.html.
URL: https://dev.mysql.com/doc/refman/5.7/en/show.html
mysql> SHOW PROFILES
-> ;
Empty set, 1 warning (0.00 sec)
mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 2 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 1131 |
+-----------------------+-------+
7 rows in set (0.00 sec)
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
mysql> show tables
-> ;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| order_0 |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
32 rows in set (0.00 sec)
mysql> drop table order_0;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
mysql> use information_schema;
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> use master;
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_master |
+------------------+
| order_0 |
+------------------+
1 row in set (0.00 sec)
mysql> select table_name from tables;
ERROR 1146 (42S02): Table 'master.tables' doesn't exist
mysql> desc order_0;
+----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| order_id | int(11) | NO | PRI | NULL | |
| user_id | int(11) | NO | | NULL | |
+----------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select schema_name from schemata\G
ERROR 1146 (42S02): Table 'master.schemata' doesn't exist
mysql> use information_schema;
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> select schema_name from information_schema.schemata\G
*************************** 1. row ***************************
schema_name: information_schema
*************************** 2. row ***************************
schema_name: master
*************************** 3. row ***************************
schema_name: mysql
*************************** 4. row ***************************
schema_name: performance_schema
*************************** 5. row ***************************
schema_name: sys
5 rows in set (0.00 sec)
mysql> select schema_name from schemata\G
*************************** 1. row ***************************
schema_name: information_schema
*************************** 2. row ***************************
schema_name: master
*************************** 3. row ***************************
schema_name: mysql
*************************** 4. row ***************************
schema_name: performance_schema
*************************** 5. row ***************************
schema_name: sys
5 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| master |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use master;
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> insert into order_0 value('4','4');
Query OK, 1 row affected (0.00 sec)
mysql> select * from order_0;
+----------+---------+
| order_id | user_id |
+----------+---------+
| 4 | 4 |
+----------+---------+
1 row in set (0.00 sec)
mysql>
mysql> use master -A;
Database changed
mysql> select * from order_0;
+----------+---------+
| order_id | user_id |
+----------+---------+
| 4 | 4 |
+----------+---------+
1 row in set (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_master |
+------------------+
| order_0 |
+------------------+
1 row in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000003 | 848 | master | mysql,information_schema,performance_schema,sys | |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)
测试完同步备库后，启用反向同步：
查看备库的状态：
mysql> show master status\G #
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 1457
Binlog_Do_DB: master
Binlog_Ignore_DB: mysql,information_schema,performance_schema,sys
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> change master to master_host='192.168.52.151', master_user='backup', master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=1457;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> show master status\G #
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 421
Binlog_Do_DB: master
Binlog_Ignore_DB: mysql,information_schema,performance_schema,sys
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> show slave status\G #
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.52.151
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1457
Relay_Log_File: rac1-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
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: 1457
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: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /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:
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>
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
在备库插入测试：
mysql> select * from order_0;
+----------+---------+
| order_id | user_id |
+----------+---------+
| 4 | 4 |
| 5 | 5 |
+----------+---------+
2 rows in set (0.00 sec)
mysql> insert into order_0 values('6','6');
Query OK, 1 row affected (0.00 sec)
mysql> select * from order_0;
+----------+---------+
| order_id | user_id |
+----------+---------+
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
+----------+---------+
3 rows in set (0.00 sec)
mysql> show master status\G #
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 1724
Binlog_Do_DB: master
Binlog_Ignore_DB: mysql,information_schema,performance_schema,sys
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> show slave status\G #
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.52.150
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 421
Relay_Log_File: rac2-relay-bin.000002
Relay_Log_Pos: 587
Relay_Master_Log_File: mysql-bin.000003
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: 421
Relay_Log_Space: 793
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: 134
Master_UUID: 21eb3371-62a8-11ea-a7f7-000c2964ad50
Master_Info_File: /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)
mysql>
此时查看主库已经有新数据：
mysql> show slave status\G #
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.52.151
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1724
Relay_Log_File: rac1-relay-bin.000002
Relay_Log_Pos: 587
Relay_Master_Log_File: mysql-bin.000002
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: 1724
Relay_Log_Space: 793
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: 135
Master_UUID: b8494768-6442-11ea-bc68-000c29df28eb
Master_Info_File: /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)
mysql> select * from order_0;
+----------+---------+
| order_id | user_id |
+----------+---------+
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
+----------+---------+
3 rows in set (0.00 sec)
mysql>
备库：
[root@rac2 ~]# mkdir /soft
[root@rac2 ~]# cd /soft
[root@rac2 soft]# ls
mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
[root@rac2 soft]# tar -zxvf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
[root@rac2 soft]# useradd mysql
[root@rac2 soft]# id mysql
uid=54323(mysql) gid=54331(mysql) groups=54331(mysql)
[root@rac2 soft]# pwd
/soft
[root@rac2 soft]# mkdir -p /mysql
[root@rac2 soft]# cd mysql-5.7.28-linux-glibc2.12-x86_64
[root@rac2 mysql-5.7.28-linux-glibc2.12-x86_64]# ls
bin docs include lib LICENSE man README share support-files
[root@rac2 mysql-5.7.28-linux-glibc2.12-x86_64]# mv * /mysql/
[root@rac2 mysql-5.7.28-linux-glibc2.12-x86_64]# chown -R mysql:mysql /mysql
[root@rac2 mysql-5.7.28-linux-glibc2.12-x86_64]#
[root@rac2 mysql-5.7.28-linux-glibc2.12-x86_64]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[client]
socket=/mysql/mysql.sock
default-character-set=utf8
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
basedir=/mysql
datadir=/mysql/data
socket=/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/mysql/logs/mysqld.log
pid-file=/mysql/mysqld.pid
explicit_defaults_for_timestamp=true
###
server-id=135
log-bin=mysql-bin
binlog-do-db=master
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
expire_logs_days=7
character-set-server=utf8
collation-server=utf8_general_ci
[mysql]
default-character-set=utf8
[root@rac2 mysql-5.7.28-linux-glibc2.12-x86_64]#
[root@rac2 mysql-5.7.28-linux-glibc2.12-x86_64]#
[root@rac2 mysql-5.7.28-linux-glibc2.12-x86_64]# mkdir -p /mysql/data
[root@rac2 mysql-5.7.28-linux-glibc2.12-x86_64]# mkdir -p /mysql/logs
[root@rac2 mysql-5.7.28-linux-glibc2.12-x86_64]# ls -l /mysql/
total 292
drwxr-xr-x 2 mysql mysql 4096 Mar 12 16:57 bin
drwxr-xr-x 2 root root 6 Mar 12 17:15 data
drwxr-xr-x 2 mysql mysql 55 Mar 12 16:57 docs
drwxr-xr-x 3 mysql mysql 4096 Mar 12 16:57 include
drwxr-xr-x 5 mysql mysql 230 Mar 12 16:57 lib
-rw-r--r-- 1 mysql mysql 279547 Sep 27 15:00 LICENSE
drwxr-xr-x 2 root root 6 Mar 12 17:15 logs
drwxr-xr-x 4 mysql mysql 30 Mar 12 16:57 man
-rw-r--r-- 1 mysql mysql 587 Sep 27 15:00 README
drwxr-xr-x 28 mysql mysql 4096 Mar 12 16:57 share
drwxr-xr-x 2 mysql mysql 90 Mar 12 16:57 support-files
[root@rac2 mysql-5.7.28-linux-glibc2.12-x86_64]# chown -R mysql:mysql /mysql
[root@rac2 mysql-5.7.28-linux-glibc2.12-x86_64]# ls -l /mysql/
total 292
drwxr-xr-x 2 mysql mysql 4096 Mar 12 16:57 bin
drwxr-xr-x 2 mysql mysql 6 Mar 12 17:15 data
drwxr-xr-x 2 mysql mysql 55 Mar 12 16:57 docs
drwxr-xr-x 3 mysql mysql 4096 Mar 12 16:57 include
drwxr-xr-x 5 mysql mysql 230 Mar 12 16:57 lib
-rw-r--r-- 1 mysql mysql 279547 Sep 27 15:00 LICENSE
drwxr-xr-x 2 mysql mysql 6 Mar 12 17:15 logs
drwxr-xr-x 4 mysql mysql 30 Mar 12 16:57 man
-rw-r--r-- 1 mysql mysql 587 Sep 27 15:00 README
drwxr-xr-x 28 mysql mysql 4096 Mar 12 16:57 share
drwxr-xr-x 2 mysql mysql 90 Mar 12 16:57 support-files
[root@rac2 mysql-5.7.28-linux-glibc2.12-x86_64]#
[root@rac2 mysql-5.7.28-linux-glibc2.12-x86_64]# cat /etc/profile
# /etc/profile
# System wide environment and startup programs, for login setup
# Functions and aliases go in /etc/bashrc
# It's NOT a good idea to change this file unless you know what you
# are doing. It's much better to create a custom.sh shell script in
# /etc/profile.d/ to make custom changes to your environment, as this
# will prevent the need for merging in future updates.
pathmunge () {
case ":${PATH}:" in
*:"$1":*)
;;
*)
if [ "$2" = "after" ] ; then
PATH=$PATH:$1
else
PATH=$1:$PATH
fi
esac
}
if [ -x /usr/bin/id ]; then
if [ -z "$EUID" ]; then
# ksh workaround
EUID=`/usr/bin/id -u`
UID=`/usr/bin/id -ru`
fi
USER="`/usr/bin/id -un`"
LOGNAME=$USER
MAIL="/var/spool/mail/$USER"
fi
# Path manipulation
if [ "$EUID" = "0" ]; then
pathmunge /usr/sbin
pathmunge /usr/local/sbin
else
pathmunge /usr/local/sbin after
pathmunge /usr/sbin after
fi
HOSTNAME=`/usr/bin/hostname 2>/dev/null`
HISTSIZE=1000
if [ "$HISTCONTROL" = "ignorespace" ] ; then
export HISTCONTROL=ignoreboth
else
export HISTCONTROL=ignoredups
fi
export PATH USER LOGNAME MAIL HOSTNAME HISTSIZE HISTCONTROL
# By default, we want umask to get set. This sets it for login shell
# Current threshold for system reserved uid/gids is 200
# You could check uidgid reservation validity in
# /usr/share/doc/setup-*/uidgid file
if [ $UID -gt 199 ] && [ "`/usr/bin/id -gn`" = "`/usr/bin/id -un`" ]; then
umask 002
else
umask 022
fi
for i in /etc/profile.d/*.sh /etc/profile.d/sh.local ; do
if [ -r "$i" ]; then
if [ "${-#*i}" != "$-" ]; then
. "$i"
else
. "$i" >/dev/null
fi
fi
done
unset i
unset -f pathmunge
export PATH=/mysql/bin:$PATH
[root@rac2 mysql-5.7.28-linux-glibc2.12-x86_64]# source /etc/profile
[root@rac2 mysql-5.7.28-linux-glibc2.12-x86_64]# which mysqld
/mysql/bin/mysqld
初始化mysql
[root@rac2 mysql-5.7.28-linux-glibc2.12-x86_64]# mysqld --initialize --user=mysql --basedir=/mysql --datadir=/mysql/data
[root@rac2 mysql-5.7.28-linux-glibc2.12-x86_64]# cd /mysql/logs
[root@rac2 logs]# ls
mysqld.log
[root@rac2 logs]# cat mysqld.log
2020-03-12T09:20:30.974123Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-03-12T09:20:31.000145Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-03-12T09:20:31.065193Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: b8494768-6442-11ea-bc68-000c29df28eb.
2020-03-12T09:20:31.067523Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-03-12T09:20:31.504237Z 0 [Warning] CA certificate ca.pem is self signed.
2020-03-12T09:20:31.549282Z 1 [Note] A temporary password is generated for root@localhost: i>;wF,EP4)3>
[root@rac2 logs]#
配置mysql服务
[root@rac2 logs]# cp -a /mysql/support-files/mysql.server /etc/init.d/mysqld
[root@rac2 logs]# chkconfig mysqld on
[root@rac2 logs]# chkconfig --list|grep mysqld
Note: This output shows SysV services only and does not include native
systemd services. SysV configuration data might be overridden by native
systemd configuration.
If you want to list systemd services use 'systemctl list-unit-files'.
To see services enabled on particular target use
'systemctl list-dependencies [target]'.
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@rac2 logs]# systemctl status mysqld
鈼mysqld.service - LSB: start and stop MySQL
Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
Active: inactive (dead)
Docs: man:systemd-sysv-generator(8)
[root@rac2 logs]# systemctl start mysqld
[root@rac2 logs]# systemctl status mysqld
鈼[0m mysqld.service - LSB: start and stop MySQL
Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
Active: active (running) since Thu 2020-03-12 17:23:31 CST; 3s ago
Docs: man:systemd-sysv-generator(8)
Process: 43865 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
CGroup: /system.slice/mysqld.service
鈹溾攢43882 /bin/sh /mysql/bin/mysqld_safe --datadir=/mysql/data --pid-file=/mysql/mysqld.pid
鈹斺攢44178 /mysql/bin/mysqld --basedir=/mysql --datadir=/mysql/data --plugin-dir=/mysql/lib/plugin --user=mysql --log-error=/mysql/logs/mysqld.log --pid-file=/mysql/mysqld.pid --socket=/mysql/mysql.sock
Mar 12 17:23:30 rac2 systemd[1]: Starting LSB: start and stop MySQL...
Mar 12 17:23:31 rac2 mysqld[43865]: Starting MySQL. SUCCESS!
Mar 12 17:23:31 rac2 systemd[1]: Started LSB: start and stop MySQL.
[root@rac2 logs]# ps -ef|grep mysql
root 43882 1 0 17:23 ? 00:00:00 /bin/sh /mysql/bin/mysqld_safe --datadir=/mysql/data --pid-file=/mysql/mysqld.pid
mysql 44178 43882 4 17:23 ? 00:00:00 /mysql/bin/mysqld --basedir=/mysql --datadir=/mysql/data --plugin-dir=/mysql/lib/plugin --user=mysql --log-error=/mysql/logs/mysqld.log --pid-file=/mysql/mysqld.pid --socket=/mysql/mysql.sock
root 44411 10595 0 17:23 pts/0 00:00:00 grep --color=auto mysql
修改root密码：
[root@rac2 logs]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.28-log
Copyright (c) 2000, 2019, 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;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> set password = password('1234');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> use mysql
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> select host,user from user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
3 rows in set (0.00 sec)
mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select host,user from user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| % | root |
| localhost | mysql.session |
| localhost | mysql.sys |
+-----------+---------------+
3 rows in set (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO "root"@"%" IDENTIFIED BY '1234' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select host,user from user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| % | root |
| localhost | mysql.session |
| localhost | mysql.sys |
+-----------+---------------+
3 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@rac2 logs]# mysql -u root -h 192.168.52.151 -p1234
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 3
Server version: 5.7.28-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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 |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
配置同步用户
[root@rac2 logs]# mysql -u root -h 192.168.52.151 -p1234
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 3
Server version: 5.7.28-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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 |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> grant replication slave on *.* to 'backup'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> use mysql
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> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user | authentication_string | host |
+---------------+-------------------------------------------+-----------+
| root | *A4B6157319038724E3560894F7F932C8886EBFCF | % |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| backup | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | % |
+---------------+-------------------------------------------+-----------+
4 rows in set (0.00 sec)
mysql> show master status\G #
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 675
Binlog_Do_DB: master
Binlog_Ignore_DB: mysql,information_schema,performance_schema,sys
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> CREATE DATABASE master;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| master |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use master;
Database changed
mysql> CREATE TABLE `order_0` (
-> `order_id` int(11) NOT NULL,
-> `user_id` int(11) NOT NULL,
-> PRIMARY KEY (`order_id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+------------------+
| Tables_in_master |
+------------------+
| order_0 |
+------------------+
1 row in set (0.00 sec)
mysql> select * from order_0;
Empty set (0.01 sec)
mysql>
mysql> insert into order_0 value('4','4');
Query OK, 1 row affected (0.01 sec)
mysql> select * from order_0;
+----------+---------+
| order_id | user_id |
+----------+---------+
| 4 | 4 |
+----------+---------+
1 row in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000002 | 2153 | master | mysql,information_schema,performance_schema,sys | |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.01 sec)
启用同步主库
mysql> show master status\G #
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 1457
Binlog_Do_DB: master
Binlog_Ignore_DB: mysql,information_schema,performance_schema,sys
Executed_Gtid_Set:
1 row in set (0.00 sec)
检查主库的状态:
mysql> show master status\G #
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 154
Binlog_Do_DB: master
Binlog_Ignore_DB: mysql,information_schema,performance_schema,sys
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> change master to master_host='192.168.52.150', master_user='backup', master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=154;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show master status\G #
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 1457
Binlog_Do_DB: master
Binlog_Ignore_DB: mysql,information_schema,performance_schema,sys
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status\G #
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 1457
Binlog_Do_DB: master
Binlog_Ignore_DB: mysql,information_schema,performance_schema,sys
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> show slave status\G #
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.52.150
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 154
Relay_Log_File: rac2-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
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: 526
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: 134
Master_UUID: 21eb3371-62a8-11ea-a7f7-000c2964ad50
Master_Info_File: /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)
在主库插入测试：
mysql> show slave status\G #
Empty set (0.00 sec)
mysql> insert into order_0 value('5','5');
Query OK, 1 row affected (0.00 sec)
mysql> show master status\G #
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 421
Binlog_Do_DB: master
Binlog_Ignore_DB: mysql,information_schema,performance_schema,sys
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> show slave status\G #
Empty set (0.00 sec)
观察备库：
mysql> select * from order_0;
+----------+---------+
| order_id | user_id |
+----------+---------+
| 4 | 4 |
| 5 | 5 |
+----------+---------+
2 rows in set (0.00 sec)
此时数据已经同步到备库，成功。
mysql> show slave status\G #
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.52.150
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 421
Relay_Log_File: rac2-relay-bin.000002
Relay_Log_Pos: 587
Relay_Master_Log_File: mysql-bin.000003
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: 421
Relay_Log_Space: 793
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: 134
Master_UUID: 21eb3371-62a8-11ea-a7f7-000c2964ad50
Master_Info_File: /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)
主备库分别做测试
结果均符合预期。主库创建新表，备库可查到。备库创建新表，插入数据，主库也可查到。
主库：
mysql> create table test(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test values(1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> select * from test1;
+------+
| id |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
备库：
mysql> select * from test;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> insert into test1 values(2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test1;
+------+
| id |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
测试关闭备库，主库继续插入操作：
备库reboot
主库插入:
mysql> use master;
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> select * from test;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> insert into test values(2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(3);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000003 | 1366 | master | mysql,information_schema,performance_schema,sys | |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)
备库启动后，自动同步数据：
mysql> show slave status\G #
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.52.150
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1366
Relay_Log_File: rac2-relay-bin.000004
Relay_Log_Pos: 838
Relay_Master_Log_File: mysql-bin.000003
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: 1366
Relay_Log_Space: 1044
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: 134
Master_UUID: 21eb3371-62a8-11ea-a7f7-000c2964ad50
Master_Info_File: /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)
mysql> select * from test;
ERROR 1046 (3D000): No database selected
mysql> use master;
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> select * from test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql>
再测试dml和truncate等操作均正常。
问题：You can turn off this feature to get a quicker startup with -A
在使用user dbname 时会出现速度慢的现象，并报提示：
MySQL> use dbname
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
然后就卡在这里。
原因是当我们打开数据库，即use dbname时，要预读数据库信息。由于数据库太大，即数据库中表非常多，所以如果预读数据库信息，将非常慢，所以就卡住了，如果数据库中表非常少，将不会出现问题。
解决办法mysql已经有所提示：
我们进入mysql 时，没有使用-A参数：
mysql> mysql -hhostname -uusername -ppassword -Pport
当使用-A参数时，就不预读数据库信息，速度就会快起来：
mysql> mysql -hhostname -uusername -ppassword -Pport –A
mysql> use master -A;
Database changed
参考文档:
双主负载均衡lvs+keeplive
主从keeplive
在线不停机同步：
转载地址：http://vmiof.baihongyu.com/