mysql主从同步
第一：检查是否开启bin_log
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
binlog的功能怎么打开
# grep log-bin /data/3306/my.cnf
log-bin = /data/3306/mysql-bin
第二：
添加一个账号允许从库来同步
mysql> grant replication slave on *.* to rep@'192.168.1.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
replication slave为同步的必须权限，此处不要授权all
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
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 |
+------------------+----------+--------------+------------------+
| mysql-bin.000019 | 635 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> show master logs;
| mysql-bin.000019 | 635 |
+------------------+-----------+
19 rows in set (0.00 sec)
备份一下主库
# mysqldump -uroot -p123456 -B -A --events -S /data/3306/mysql.sock |gzip >/opt/all.sql.gz
或者
# mysqldump -uroot -p123456 -B -A --events -S /data/3306/mysql.sock --master-data=2 >/opt/rep.sql
如果master-data=1，就自动记录了当前备份的位置点了，导入到从库的时候就记录的是哪个位置了。
查看备份的数据库，vi /opt/rep.sql 搜索
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000019', MASTER_LOG_POS=635;
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
到此主库就可以提供服务了
以上是先锁表，备份数据库，然后再解锁表。
第三：
导入主库到从库
# mysql -S /data/3307/mysql.sock</opt/rep.sql
登录从库
# mysql -S /data/3307/mysql.sock
在从库执行
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.1.182',
-> MASTER_PORT=3306,
-> MASTER_USER='rep',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000019',
-> MASTER_LOG_POS=635;
上面执行后生成master.info 文件放在从库的data目录下面
然后开启同步开关
mysql> start slave;
查看状态
mysql> show slave status\G;
测试同步结果：
主库执行
mysql> create database wiki;
从库查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| mysql |
| performance_schema |
| test |
| wiki |
+--------------------+
同步成功
页:
[1]