一. 环境搭建
主master:192.168.76.133
从slave: 192.168.76.134
建立测试用数据库及数据表
mysql> create table employees(
-> employee_id INT NOT NULL AUTO_INCREMENT,
-> name char(20) NOT NULL,
-> e_mail varchar(50),
-> PRIMARY KEY(employee_id));
mysql> desc employees;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| employee_id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | NULL | |
| e_mail | varchar(50) | YES | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
插入数据
mysql> insert into employees values
-> (1,'TOM','tom@example.com'),
-> (2,'Jerry','jerry@example.com');
mysql> select * from employees;
+-------------+-------+-------------------+
| employee_id | name | e_mail |
+-------------+-------+-------------------+
| 1 | TOM | tom@example.com |
| 2 | Jerry | jerry@example.com |
+-------------+-------+-------------------+
二. 主库服务器配置
1. 设置服务器编号,开启二进制日志
编辑/etc/my.cnf文件
[mysqld]
server-id = 1 #设置服务器编号
log-bin = /usr/local/mysql/data/mysql-bin/binlog
#启用二进制日志 设定路径名 此目录mysql有权写入.
#该路径指定了存放二进制日志的目录为mysql-bin 日志文件名前缀为binlog
-------可选------------------------------
#binlog-do-db = 数据库名 指定要同步的数据库
#binlog-ignore-db=数据库名 指定不同的数据库
重启mysqld服务使配置生效
service mysqld restart
查看是否生效
cd /usr/local/mysql/data/mysql-bin/
[root@wzz mysql-bin]# ll
total 8
-rw-rw----. 1 mysql mysql 106 Feb 16 12:23 binlog.000001
-rw-rw----. 1 mysql mysql 46 Feb 16 12:23 binlog.index
2. 创建从库复制账号
mysql> grant replication slave on *.* to 'rep'@'192.168.76.134' identified by 'slave';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
3. 只读锁定拒绝写操作
mysql> flush tables with read lock;
4.获取当前日志文件和记录点
mysql> show master status;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000001 | 334 | | |
+---------------+----------+--------------+------------------+
5. 新开XShell窗口,dump数据库
dump数据
mysqldump -uroot -poldboy -A -B --events --master-data=2 >/opt/rep.sql
备份拷贝至从库服务器
scp /opt/rep.sql root@192.168.76.134:/home/wzz/ #拷贝至从库
主库解锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
备份灌到从库
mysql -uroot -poldboy < /home/wzz/rep.sql # 在从库执行此操作
三. 从库服务器配置
1. 设置从库ID
主从库ID必须唯一 修改my.cnf 后 重启mysql生效
server-id = 2
#log-bin=mysql-bin #从库不级联,可以不开启log-bin
重启mysql使配置生效
2. CHANGE MASTER TO设置连接信息
通过CHANGE MASTER TO
指定从库连接主库时所必须的信息
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.76.133',
MASTER_PORT=3306, # 注意数字不能有引号
MASTER_USER='rep',
MASTER_PASSWORD='slave',
MASTER_LOG_FILE='binlog.000001',
MASTER_LOG_POS=334; # 注意数字不能有引号
这些信息会存放在从库data目录下的master.info里。(我的data目录位于/usr/local/mysql)
[root@wzz ~]# cat /usr/local/mysql/data/master.info
18
binlog.000001
334
192.168.76.133
rep
slave
3306
60
0
3. 从库开始同步
开启从库同步
mysql> start slave
检查从库状态
mysql> show slave status\G
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.76.133
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 334
Relay_Log_File: nagios-client03-relay-bin.000002
Relay_Log_Pos: 249
Relay_Master_Log_File: binlog.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: 334
Relay_Log_Space: 415
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
四. 测试
主库的增删改操作会自动同步到从库上
relay-log.info
master.info
show processlist\G
show master status\G
show slave status\G