MySQL主从配置尝试(同一台机器)

作为一个Oracle 老人，看着MySQL越来越火。如果不学习，似乎感觉跟不上时代和节奏；听说有Oracle基础，学习起来非常容易。那咋也来尝试一下吧。在不看任何文档的情况下，自己摸索配置一下mysql主从，当然有问题可以Google或者百度。

1、主库配置my.cnf

mysql> use roger;
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> create user rep_test identified by 'rep_test' password expire interval  180 day;
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to 'rep_test'@'172.16.29.%' identified by 'rep_test';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name        | File_size |
+------------------+-----------+
| mysql_bin.000001 |      743 |
+------------------+-----------+
1 row in set (0.00 sec)

2、修改主库配置文件

[mysqld]
socket=/var/lib/mysql/mysql.sock
port            = 3306
pid-file        = /var/run/mysqld/mysqld.pid
datadir        = /opt/mysql
user            = mysql
log-error      =/opt/mysql/mysqld_pri.log
log-bin=/opt/mysql/bin_log/log-bin.log
#log-bin=mysql-bin
server-id=1
validate_password_policy=low
#max_open_files=5000
#table_open_cache=2000
symbolic-links=0
log-bin-index = /opt/mysql/log-bin.index
sql_mode=”STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”
sync_binlog=1
innodb_flush_log_at_trx_commit=1
expire_logs_days = 30
max_binlog_size = 100M
binlog_cache_size = 4M
max_binlog_cache_size = 512M
binlog-do-db = roger
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema

3、创建新用户，作为从库使用用户，以及创建相应目录

useradd mysql2
passwd mysql2
usermod mysql2 -g mysql
[root@mysqldb opt]# mkdir -p mysql_slave
[root@mysqldb opt]# chown -R mysql2:mysql /opt/mysql_slave/

4、修改从库配置文件

这里直接利用主库的配置文件进行修改编辑，编辑后如下：

socket=/opt/mysql_slave/mysql_slave.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/opt/mysql_slave/mysqld_slave.log
pid-file=/opt/mysql_slave/mysqld_slave.pid
#user=mysql2
datadir=/opt/mysql_slave/mysql
#scoket=/opt/mysql/mysql.sock
###config for pri-salve
log-bin=mysql_bin
server-id=2
#validate_password_policy=low
#max_open_files=5000
#table_open_cache=2000
###
port=3307
#validate_password_policy=low
sql_mode=”STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”
#master-host = ‘172.16.29.131’
#master-user = rep_test
#master-password = rep_test
#master-port = 3306
#master-connect-retry = 30
replicate-do-db=roger
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
relay-log=/opt/mysql_slave/relay_log/relay-bin
relay-log-index=/opt/mysql_slave/relay_log/relay-bin-index
skip-grant-tables

5、从库初始化

[root@mysqldb mysql_slave]# mysqld --defaults-file=/opt/mysql_slave/my.cnf --initialize --user=mysql2 --datadir=/opt/mysql_slave/mysql --explicit_defaults_for_timestamp
[root@mysqldb mysql_slave]#

6、备份主库需要同步的数据库

mysql>  reset master;
Query OK, 0 rows affected (0.00 sec)
mysql> reset SLAVE;
Query OK, 0 rows affected (0.01 sec)
[root@mysqldb bin_log]# mysqldump –databases roger –master-data -uroot -p > /tmp/roger_full.db
Enter password:
[root@mysqldb bin_log]# ls -ltr /tmp/roger_full.db
-rw-r–r– 1 root root 3543223 Jul 20 15:57 /tmp/roger_full.db
[root@mysqldb bin_log]#

7、将数据导入到从库中

[root@mysqldb mysql_slave]# /bin/mysql -S /opt/mysql_slave/mysql_slave.sock < /tmp/mysql_roger.db

[root@mysqldb mysql_slave]#

8、启动从库同步

mysql> select count(1) from tt;
+———-+
| count(1) |
+———-+
|  1310720 |
+———-+
1 row in set (0.28 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: rep_test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: log-bin.000004
Read_Master_Log_Pos: 5533674
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 2764840
Relay_Master_Log_File: log-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: roger
Replicate_Ignore_DB: mysql,information_schema,performance_schema
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: 5533674
Relay_Log_Space: 2765041
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: 7e865a35-50d8-11e7-a35f-000c29849e4a
Master_Info_File: /opt/mysql_slave/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

9、查看主库情况

mysql> show master status;
+—————-+———-+————–+———————————————+——————-+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB           | Executed_Gtid_Set |
+—————-+———-+————–+———————————————+——————-+
| log-bin.000004 |  5533674 | roger        | mysql,information_schema,performance_schema |                  |
+—————-+———-+————–+———————————————+——————-+
1 row in set (0.00 sec)
mysql>  select count(1) from tt;
+———-+
| count(1) |
+———-+
|  1310720 |
+———-+
1 row in set (0.27 sec)

10、启动从库数据同步

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

11、主从数据同步验证

–主库插入数据

mysql> insert into tt values(999999);
Query OK, 1 row affected (0.01 sec)

mysql>  exit

–从库查询数据

mysql> select * from tt where a=999999;
+——–+
| a      |
+——–+
| 999999 |
+——–+
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: 127.0.0.1
Master_User: rep_test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: log-bin.000004
Read_Master_Log_Pos: 5533929
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 2765095
Relay_Master_Log_File: log-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: roger
Replicate_Ignore_DB: mysql,information_schema,performance_schema
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: 5533929
Relay_Log_Space: 2765296

其中遇到如下一些问题：

2017-07-20T04:35:16.603805Z 0 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Table 'mysql.plugin' doesn't exist
2017-07-20T04:35:16.603952Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.

该问题主要原因是从库进行数据库初始化有问题，后面重新通过命令初始化之后解决该问题。

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’

通过mysqlbinlog 分析日志，发现日志似乎不连续，因此通过重新mysqldump 备份roger数据库然后倒入后，解决该问题。

root@mysqldb mysql_slave]# mysqlbinlog –start-position=154 /opt/mysql_slave/relay_log/relay-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170720 15:32:23 server id 2  end_log_pos 123 CRC32 0x85271cd4  Start: binlog v 4, server v 5.7.16-log created 170720 15:32:23 at startup
# This Format_description_event appears in a relay log and was generated by the slave thread.
SET @@SESSION.GTID_NEXT= ‘AUTOMATIC’ /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@mysqldb mysql_slave]# mysqlbinlog  /opt/mysql_slave/relay_log/relay-bin.000001 > /tmp/log_bin_analyze.txt
[root@mysqldb mysql_slave]#
[root@mysqldb mysql_slave]# cat /tmp/log_bin_analyze.txt
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170720 15:32:23 server id 2  end_log_pos 123 CRC32 0x85271cd4  Start: binlog v 4, server v 5.7.16-log created 170720 15:32:23 at startup
# This Format_description_event appears in a relay log and was generated by the slave thread.
# at 123
#170720 15:32:23 server id 2  end_log_pos 154 CRC32 0xd3e2eedc  Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= ‘AUTOMATIC’ /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
Last_IO_Error: Master command COM_REGISTER_SLAVE failed: Access denied for user ‘rep_test’@’%’ (using password: YES) (Errno: 1045)

该问题主要原因是用于复制的用户没法进行连接访问主库，重新授权后解决。

grant replication slave on *.* to ‘rep_test’@’127.0.0.%’ identified by ‘rep_test’;

总的来讲，整个主从配置过程还是比较简单，个人感觉比Oracle DataGuard要来的容易一些。从今天起正式开启MySQL学习之旅！