本帖最后由 seesea2517 于 2017-03-02 10:04 编辑
前期准备: 两台安装MySQL的服务器,或者在一台服务器上部署两个MySQL实例。为了避免出现不必要的错误,MySQL版本最好保持一致。 +----------------+----------+-------------+-----------+----------+----------+ |服务器地址 |主机名 |数据库版本 |数据库端口 |server_id |角色 | +----------------+----------+-------------+-----------+----------+----------+ |192.168.175.248 |Mysql-248 |Mysql-5.6.30 |3306 |1 |主库Master| +----------------+----------+-------------+-----------+----------+----------+ |192.168.175.249 |Mysql-249 |Mysql-5.6.30 |3306 |2 |从库Slave | +----------------+----------+-------------+-----------+----------+----------+ 一、 主库配置: 1. 开启二进制日志,配置server_id(需要重启生效)。 [root@Mysql-248 mysql-5.6.30]# grep -A3 'mysqld' my.cnf [mysqld] port = 3306 server_id = 1 log-bin=mysql-bin 检验二进制日志状态,ON为打开: mysql> show variables like 'log_bin' ; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec) 2. 在主库建立Mysql复制用户。 mysql> grant replication slave on *.* to 'repl_user'@'192.168.175.%' identified by '123456'; 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; Query OK, 0 rows affected (0.01 sec) 查看master状态信息: mysql> show master status; +------------------+----------+-------------+------------------+-------------------+ | File | Position | Binlog_Do_DB| Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+-------------+------------------+-------------------+ | mysql-bin.000001 | 414 | | | | +------------------+----------+-------------+------------------+-------------------+ 1 row in set (0.00 sec) 新建ssh窗口,备份数据库: [root@Mysql-248 ~]# mysqldump -uroot -p'qwe123``' -A > /tmp/master248.sql 备份完成后,在原先窗口中解锁: mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) 二、从库配置: 1. 配置从库server_id与relay-log(需要重启生效)。 注意:server_id必须是唯一的,不能与其他mysql库相同。从库无需开启二进制日志。 [root@Mysql-249 mysql-5.6.30]# grep mysqld -A3 my.cnf [mysqld] port = 3306 server_id = 2 relay-log = mysql-relay-bin 2. 将主库的备份拷贝到本机,导入数据库。 拷贝备份: root@192.168.175.248's password: master248.sql 导入: [root@Mysql-249 mysql-5.6.30]# mysql -uroot -p'qwe123``' < /tmp/master248.sql Warning: Using a password on the command line interface can be insecure. 3. 指定master服务器信息,开启slave。 指定master信息: mysql> change master to \ -> master_host='192.168.175.248', -> master_user='repl_user', -> master_password='123456', -> master_log_file='mysql-bin.000001', -> master_log_pos=414; Query OK, 0 rows affected, 2 warnings (0.03 sec) 开启slave: mysql> start slave; Query OK, 0 rows affected (0.01 sec) 三、检验主从复制: 1. 在从库使用show slave status\G,查询主库信息以及IO进程、SQL进程工作状态。 mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.175.248 Master_User: repl_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 414 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes ...... 1 row in set (0.00 sec) 查询结果显示Slave_IO_Running: Yes,Slave_SQL_Running: Yes,表示当前主从复制状态正常。 2. 在master新建数据库,在slave查询,测试主从复制效果。 Master建库建表。 mysql> create database cubix character set utf8; Query OK, 1 row affected (0.00 sec) mysql> use cubix Database changed mysql> create table T1 (id int); Query OK, 0 rows affected (0.02 sec) mysql> insert into T1 VALUES ('1'); Query OK, 1 row affected (0.00 sec)
mysql> insert into T1 VALUES ('2'); Query OK, 1 row affected (0.00 sec) mysql> insert into T1 VALUES ('3'); Query OK, 1 row affected (0.01 sec)
Slave查询新建的库。 mysql> show databases; +-------------------+ | Database | +-------------------+ | information_schema| | cubix | | mysql | | performance_schema| +-------------------+ 6 rows in set (0.00 sec)
mysql> use cubix Database changed mysql> show tables; +----------------+ | Tables_in_cubix| +----------------+ | T1 | +----------------+ 1 row in set (0.00 sec)
mysql> select * from T1; +------+ | id | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec)
检查发现在主库上新增的数据,在从库上也有了,也可以证明主从同步正常。
|