主从配置 步骤如下
1、主服务器ip:192.168.2.128 从服务器ip:192.168.2.129
2、修改主服务器的master
vim /usr/local/mysql/my.cnf
[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=mysql_bin #[必须]启用二进制日志
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
server_id = 1 #[必须]服务器唯一ID,默认是1,一般取IP最后一段
# socket = .....
:wq //保存并退出
3、修改从服务器的slave
vim /usr/local/mysql/my.cnf
[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=mysql_bin #[不是必须]启用二进制日志
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
server_id = 2 #[必须]服务器唯一ID,默认是1,一般取IP最后一段
# socket = .....
:wq //保存并退出
4、重启mysql
service mysql restart
5、在主服务器上建立帐户并授权slave:
进入mysql
mysql> GRANT REPLICATION SLAVE ON *.* to 'user'@'%' identified by '123123'; //一般不用root帐号,“%”表示所有客户端都可能连,只要帐号,密码正确,此处可用具体客户端IP代替,如192.168.2.129,加强安全。
Query OK, 0 rows affected (0.01 sec)
6、登录主服务器的mysql,查询master的状态
mysql> show master status;
+------------------+----------+--------------+------------------+----------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_S
+------------------+----------+--------------+------------------+----------------
| mysql_bin.000001 | 332 | | |
+------------------+----------+--------------+------------------+----------------
1 row in set (0.00 sec)
注:执行完此步骤后不要再操作主服务器MYSQL,防止主服务器状态值变化
7、配置从服务器Slave:
注意mysql_bin.000001和332是第6步中的File和Position
mysql> change master to master_host='192.168.2.128',master_user='user',master_password='123123',master_log_file='mysql_bin.000001',master_log_pos=332;
Query OK, 0 rows affected, 2 warnings (0.12 sec)//注意mysql-bin.000004和308是第6步中的File和positoinmysql> start slave;//启动从服务器复制功能
Query OK, 0 rows affected (0.07 sec)
8.、检查从服务器复制功能状态:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.128
Master_User: mysync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000005
Read_Master_Log_Pos: 120
Relay_Log_File: root-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql_bin.000005
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: 120
Relay_Log_Space: 455
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: b0b84215-b69a-11e7-beeb-000c293251cd
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 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)
ERROR:
No query specified
如果显示这些,那么恭喜你主从复制配置成功!!!
注意:两个必须都是为yes说明才是成功的;
如果你的 Slave_IO_Running: NO 一般出现以下情况
<1> mysql从库上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是你从主服务器上复制来的 ,那么他们的server_uuid是一样的,那么去从服务器上改一下就好了,mysql 5.6的复制引入了uuid的概念,各个复制结构中的server_uuid得保证不一样,但是查看到直接copy data文件夹后server_uuid是相同的
解决方法 vim /usr/local/mysql/data/auto.cnf server_uuid改一下不要两个id一样
<2>mysql从库上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'报错
解决方法
首先在从库上执行
stop slave;
查看主库master状态
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql_bin.000001
Position: 332
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
刷新binlog日志
flush logs;
刷新后的日志会+1
例如上面的 File: mysql_bin.000001 会变成 File: mysql_bin.000002
再次查看master状态
然后就不需要在操作master,切换到从库
执行start slave;
查看从库状态
mysql> show slave status\G; 如果都没有问题就会同上显示从库的状态;
测试
主从服务器测试:
主服务器Mysql,建立数据库,并在这个库中建表插入一条数据:
- mysql> create database hi_db;
- Query OK, 1 row affected (0.00 sec)
- mysql> use hi_db;
- Database changed
- mysql> create table hi_tb(id int(3),name char(10));
- Query OK, 0 rows affected (0.00 sec)
- mysql> insert into hi_tb values(001,'bobu');
- Query OK, 1 row affected (0.00 sec)
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | hi_db |
- | mysql |
- | test |
- +--------------------+
- 4 rows in set (0.00 sec)
- 从服务器Mysql查询:
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | hi_db | //I'M here,大家看到了吧
- | mysql |
- | test |
- +--------------------+
- 4 rows in set (0.00 sec)
- mysql> use hi_db
- Database changed
- mysql> select * from hi_tb; //查看主服务器上新增的具体数据
- +------+------+
- | id | name |
- +------+------+
- | 1 | bobu |
- +------+------+
- 1 row in set (0.00 sec)