MySQL主从复制
先说一下服务器环境吧,MySQL5.6,使用centos-6.9-64位操作系统,共两台,因为这个文档很久之前就写完了,现在开来之前写的有点太草率了,有必要重新写一下,正好赶上个机会,现在又要做一套主从粗来,也是按着这篇文档来做的,顺便把文档也更新一下吧,至于是MySQL安装嘛,我是真心不喜欢编译安装了,而是用yum装的,具体参考一下这里。
主库配置
1.修改my.cnf配置文件,开启binlog,定义server-id
[root@master ~]# vim /etc/my.cnf
server-id=1 #server id 主的等于1
log-bin=/data/mysql/mysql-bin #开启binlog
[root@master ~]# /etc/init.d/mysqld restart
如果没有定义root密码,需要设置一下密码
mysql> use mysql;
mysql> UPDATE user SET Password = PASSWORD('password') WHERE user = 'root';
mysql> FLUSH PRIVILEGES;
2.查看配置是否生效
mysql> show variables like 'server_id';
+---------------+-------+
| server_id | 1 | #server_id 必须等于1
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'log_bin';
+---------------+-------+
| log_bin | ON | #ON表示binlog功能已开启
+---------------+-------+
1 row in set (0.00 sec)
3.创建复制用户
mysql> grant replication slave on * .* to 'slave'@'10.10.56.%' identified by 'password';
Query OK, 0 rows affected (0.01 sec)
#用户slave 密码password
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
#重新加载权限
该用户只是允许在10.10.56.0网段登陆,这个创建的时候看自己的网段,适当修改即可。
4.锁表,查看服务器状态
mysql> flush table with read lock ; ##锁表
Query OK, 0 rows affected (0.00 sec)
mysql> show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 1104 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
5.导出主库全部数据,将sql文件传到从服务器
[root@master ~]# mysqldump -u root -p --events --ignore-table=mysql.events --all-databases > all.sql
Enter password: #输入密码
[root@master ~]# scp all.sql root@10.10.56.104:/root/all.sql
配置从服务器
1.导入主库的sql文件
[root@slave ~]# mysql -u root -p
Enter password: #输入密码
mysql> source /root/all.sql #导入主库的sql
2.定义从库的server-id
[root@slave ~]# vim /etc/my.cnf
server-id=2
[root@slave ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
注意,由于从库的全部数据都是在主库导过来的,所以现在它的一切都和主库一样,包括root密码,所以现在从库的root密码和主库一致。
3.查看从库配置是否生效
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF | #没有开始binlog
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 2 | #server-id是你定义的
+---------------+-------+
1 row in set (0.00 sec)
4.最后,执行同步语句。
mysql> CHANGE MASTER TO MASTER_HOST='10.10.56.175',MASTER_PORT=3306,MASTER_USER='slave',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=1104;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
#同步语句
mysql> start slave ;
Query OK, 0 rows affected (0.01 sec)
#开始同步
5.查看状态
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G #查看状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.56.175
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1104
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes #两个yes即可
验证效果
1.先在主库搞点事情
mysql> create database rj_bai;
Query OK, 1 row affected (0.00 sec)
#主库创建rj_bai数据库;
mysql> use rj_bai ;
Database changed
mysql> create table rj_bai(id int(3),name char(10));
Query OK, 0 rows affected (0.02 sec)
#创建rj_bai表
mysql> insert into rj_bai values (001,'rj-bai');
Query OK, 1 row affected (0.00 sec)
#插入记录rj-bai
mysql> select * from rj_bai ;
+------+--------+
| id | name |
+------+--------+
| 1 | rj-bai |
+------+--------+
1 row in set (0.00 sec)
#查看记录
2.查看从库是否有记录
mysql> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| rj_bai |
+--------------------+
4 rows in set (0.01 sec)
mysql> use rj_bai ;
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_rj_bai |
+------------------+
| rj_bai |
+------------------+
1 row in set (0.00 sec)
mysql> select * from rj_bai ;
+------+--------+
| id | name |
+------+--------+
| 1 | rj-bai |
+------+--------+
1 row in set (0.00 sec)
可以看到数据,大功告成。
本作品采用 知识共享署名-相同方式共享 4.0 国际许可协议 进行许可。