MySQL主从配置尝试(同一台机器)
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: MySQL主从配置尝试(同一台机器)
作为一个Oracle 老人，看着MySQL越来越火。如果不学习，似乎感觉跟不上时代和节奏；听说有Oracle基础，学习起来非常容易。那咋也来尝试一下吧。在不看任何文档的情况下，自己摸索配置一下mysql主从，当然有问题可以Google或者百度。
1、主库配置my.cnf
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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、修改主库配置文件
|
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 |
[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、创建新用户，作为从库使用用户，以及创建相应目录
|
1 2 3 4 5 |
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、修改从库配置文件
这里直接利用主库的配置文件进行修改编辑，编辑后如下：
|
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 |
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、从库初始化
|
1 2 |
[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、备份主库需要同步的数据库
|
1 2 3 4 5 6 7 8 9 |
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、将数据导入到从库中
|
1 2 3 |
[root@mysqldb mysql_slave]# /bin/mysql -S /opt/mysql_slave/mysql_slave.sock < /tmp/mysql_roger.db [root@mysqldb mysql_slave]# |
8、启动从库同步
|
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 |
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、查看主库情况
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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、启动从库数据同步
|
1 2 |
mysql> start slave; Query OK, 0 rows affected (0.01 sec) |
11、主从数据同步验证
–主库插入数据
|
1 2 3 4 |
mysql> insert into tt values(999999); Query OK, 1 row affected (0.01 sec) mysql> exit |
–从库查询数据
|
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 |
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 |
其中遇到如下一些问题：
|
1 2 3 |
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数据库然后倒入后，解决该问题。
|
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 |
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学习之旅！
Leave a Reply
You must be logged in to post a comment.