主从数据库配置、错误解决、Laravel框架中的主从配置
祁嘉言
- 主库：
1.授权复制账号使用的用户REPLICATION SLAVE权限：
GRANT REPLICATION SLAVE ON *.* to 'slaver'@'192.168.0.203' identified by 'cpf123456'
2.修改数据库配置文件my.cnf,开启binlog，并设置server-id的值
[mysqld]
log-bin=mysql-bin # 在mysql安装目录的data中可以看到二进制文件
server-id=1 # 主控设置为1
修改完成需要重启MySQL服务才能生效，重新启动：
(1).mysqladmin -u root -p shutdown（关闭）
(2).mysqld_safe -u mysql &（重启）
或者：
service mysqld restart
3.在主库上，设置读锁定有效，这个操作是为了确保没有数据库操作，以便获得一个一致性的快照；
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.05 sec)
4.查看当前主服务器信息（二进制日志名和偏移量值，目的是为了在从数据库启动以后，从这个点开始进行数据的恢复）
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
5.备份主数据库的数据到从数据库,mysqldump 或者其他方法，如果著数据库服务可以停止，可以直接复制data文件夹，这种方式复制很快
6.数据备份完毕后，恢复写操作，剩下的操作只需在从库上执行
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
- 从数据库
1.修改从数据库的配置文件my.cnf,添加server-id参数。该参数必须唯一，不能和主数据库相同，如果有多个从库，每个从库必须有自己唯一的server-id
[mysqld]
server-id=2
2.重启时使用–skip-slave-start选项启动从数据库，这样不会立即启动从数据库服务上的复制进程，方便我们对从数据库进行进一步的配置
重新启动 1.mysqladmin -u root -p shutdown（关闭） 2.mysqld_safe --skip-slave-start &（重启）
3.对从库进行相应设置
参数说明：
MASTER_HOST：主库IP
MASTER_PORT:主库端口
MASTER_USER：主库复制使用的用户 ，主库第1步设置的
MASTER_PASSWORD：复制用户密码，主库第1步设置的
MASTER_LOG_FILE：开始执行复制的日志文件
MASTER_LOG_POS：开始执行复制的日志位置
mysql> CHANGE MASTER TO
->MASTER_HOST='192.168.0.238',
->MASTER_PORT=3306,
->MASTER_USER='slaver',
->MASTER_PASSWORD='cpf123456',
->MASTER_LOG_FILE='mysql-bin.000001',
->MASTER_LOG_POS=120;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
4.启动slave线程
mysql> start slave (停止是stop slave)
Query OK, 0 rows affected (0.00 sec)
5.状态查询：
mysql> show processlist\G
*************************** 1. row ***************************
Id: 1
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: show processlist
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 89
State: Connecting to master
Info: NULL
*************************** 3. row ***************************
Id: 3
User: system user
Host:
db: NULL
Command: Connect
Time: 89
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
3 rows in set (0.00 sec)
表示slave已经连上master，并开始接受并执行日志。
从上面看不出有什么问题，执行show slave status; 可以看到以下信息：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.0.238
Master_User: slaver
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 120
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Connecting
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: 120
Relay_Log_Space: 120
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: 1045
Last_IO_Error: error connecting to master 'slaver@192.168.0.238:3306' - retry-time: 60 retries: 1 （这是因为在上面第3步设置的时候密码写错了，stop slave之后重新执行第3步即可）
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /opt/lamp/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 190307 10:56:32
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
- 测试
– 在主库上执行增、删、改操作，查看从库状态
mysql> insert into zhihu_user (name)values ('lisi'),('wangwu'),('zhaoliu');
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from zhihu_user;
+------+----------+----------+-----------+------------+
| id | name | follower | following | created_at |
+------+----------+----------+-----------+------------+
| NULL | cpftest | NULL | NULL | NULL |
| NULL | zhangsan | NULL | NULL | NULL |
| NULL | lisi | NULL | NULL | NULL |
| NULL | wangwu | NULL | NULL | NULL |
| NULL | zhaoliu | NULL | NULL | NULL |
+------+----------+----------+-----------+------------+
5 rows in set (0.00 sec)
– 从库查询：
mysql> select * from zhihu_user;
+------+----------+----------+-----------+------------+
| id | name | follower | following | created_at |
+------+----------+----------+-----------+------------+
| NULL | cpftest | NULL | NULL | NULL |
| NULL | zhangsan | NULL | NULL | NULL |
| NULL | lisi | NULL | NULL | NULL |
| NULL | wangwu | NULL | NULL | NULL |
| NULL | zhaoliu | NULL | NULL | NULL |
+------+----------+----------+-----------+------------+
5 rows in set (0.00 sec)
至此配置完成。
下面是在配置过程中遇到过的问题：
- 问题一：
执行mysql及mysqladmin的时候提示：
error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)'
详细：
mysqladmin -u root -p shutdown
Enter password:
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists!
解决：
1.登录数据库
TCP连接：
mysql --protocol=TCP -uroot -p -P3306 -hlocalhost
# mysql --protocol=TCP -uroot -p -P3306 -hlocalhost
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.32 Source distribution
Copyright (c) 2000, 2016, 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.
2.退出，重启mysql服务：
service mysqld restart
此时发现/tmp下已经生成了mysql.sock
-
问题2：
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.0.238
Master_User: slaver
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1170
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
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: 1170
Relay_Log_Space: 120
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: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID:
Master_Info_File: /opt/lamp/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 190307 13:27:09
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
解决：
手动修改UUID的值。
mysql> show variables like '%server_uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 9d683079-d434-11e6-ba9d-000c29a2888b |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
vi /mysql-path/data/auto.cnf
修改uuid的值，使其不同于主服务器中对应的值。修改完成后重启MySQL服务。
service mysqld restart
重启完成后，在mysql命令界面再次输入上述命令验证。
mysql> show variables like '%server_uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 9d683079-d434-11e6-ba9d-000c29a2999a |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
修改完成后重启slave:
mysql> stop slave;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.238
Master_User: slaver
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1170
Relay_Log_File: localhost-relay-bin.000005
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.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: 1170
Relay_Log_Space: 623
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: 9d683079-d434-11e6-ba9d-000c29a2888b
Master_Info_File: /opt/lamp/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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
1 row in set (0.00 sec)
可以看出此时已经没有错误了。再次做主从同步的试验，成功！
- Laravel框架中的主从配置
参考laravel学院文档：https://laravelacademy.org/post/6947.html
注：本文配置过程和报错参考《深入浅出MySQL数据库开发、优化与管理维护》一书。
类似资料：