配置Mysql数据库的主从同步教程
主从同步是实现网站分布式数据处理一个非常常用的方案了,今天我来为各位介绍配置Mysql数据库的主从同步(双主)教程,希望下文能帮助到各位哦.
配置Mysql数据库的主从同步(一主一从).
一、主库开启BINLOG、server-id
[root@Master-Mysql ~]# grep -E \ log-bin = /usr/local/mysql/data/mysql-bin server-id = 1
mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------------+
| Variable_name | Value +---------------------------------+---------------------------------------+
| log_bin | ON | log_bin_basename | /usr/local/mysql/data/mysql-bin | | log_bin_index | /usr/local/mysql/data/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | sql_log_bin | ON +---------------------------------+---------------------------------------+ 6 rows in set (0.01 sec) --phpfensi.com mysql> show variables like '%server_id%'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | server_id | 1 | | server_id_bits | 32 | +----------------+-------+ 2 rows in set (0.00 sec)
备注:以上两个信息必须在[mysqld]模块下！！！
二、给从库授权
mysql> grant replication slave on *.* to byrd@'192.168.199.%' identified by 'admin'; mysql> flush privileges;
mysql> select user,host from mysql.user; +------+---------------+ | user | host | +------+---------------+ | root | 127.0.0.1 | | byrd | 192.168.199.% |
| | | | | root | ::1 | | root | lamp | | root | localhost | +------+---------------+ 5 rows in set (0.00 sec) 锁表前建立点数据:
mysql> create database hitest; mysql> show databases; +--------------------+
| Database | +--------------------+ | hitest | +--------------------+ 6 rows in set (0.00 sec) mysql> use hitest;
mysql> create table test(
-> id int(4) not null primary key auto_increment, -> name char(20) not null -> );
Query OK, 0 rows affected (1.80 sec) mysql> show tables ; +------------------+ | Tables_in_hitest | +------------------+ | test | +------------------+
mysql> insert into test(id,name) values(1,'zy'); mysql> select * from test; +----+------+ | id | name | +----+------+ | 1 | zy | +----+------+
三、锁表、备份、解锁
mysql> flush table with read lock; #锁表
mysql> show variables like '%timeout%'; #锁表时间 +-----------------------------+----------+
| Variable_name | Value | +-----------------------------+----------+
| interactive_timeout | 28800 | | wait_timeout | 28800 | +-----------------------------+----------+
12 rows in set (0.06 sec)
mysql> show master status; #binlog日志位置
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 1305 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.03 sec)
[root@Master-Mysql ~]# /usr/local/mysql/bin/mysqldump -uroot -p'' -B -A |gzip >/tmp/all.sql.gz #新窗口备份 Enter password:
mysql> unlock table; #解锁
###############解锁后主库操作如下：############### mysql> use hitest
mysql> insert into test(id,name) values(2,'binghe'); mysql> select * from test; +----+--------+ | id | name | +----+--------+ | 1 | zy | | 2 | binghe | +----+--------+
mysql> create database hxy;
###############解锁后主库操作完成~############### 备注:备份数据需要重新打开新窗口,不然锁表就自动失效.
四、主库导入到从库
################主库操作################ [root@Master-Mysql tmp]# ll
-rw-r--r--. 1 root root 162236 Jul 8 21:30 all.sql.gz [root@Master-Mysql tmp]# gzip -d all.sql.gz [root@Master-Mysql tmp]# ll
-rw-r--r--. 1 root root 590351 Jul 8 21:30 all.sql ################主库完成################
##备注：将主库导出的all.sql通过scp、ssh、sftp等方式拷贝到从库服务器，此处略## [root@Slave-Mysql ~]# grep log-bin /etc/my.cnf #log-bin = /usr/local/mysql/data/mysql-bin
[root@Slave-Mysql ~]# grep server-id /etc/my.cnf server-id = 2
[root@Slave-Mysql ~]# /etc/init.d/mysqld restart
[root@Slave-Mysql tmp]# /usr/local/mysql/bin/mysql -uroot -p'admin'
mysql> use hitest;
mysql> select * from test; +----+------+ | id | name | +----+------+ | 1 | zy | +----+------+
1 row in set (0.00 sec) 六、从库配置信息
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.199.177', -> MASTER_PORT=3306, -> MASTER_USER='byrd',
-> MASTER_PASSWORD='admin',
-> MASTER_LOG_FILE='mysql-bin.000004', -> MASTER_LOG_POS=1305;
Query OK, 0 rows affected, 2 warnings (1.96 sec)
[root@Slave-Mysql ~]# ll /usr/local/mysql/data/master.info ##备注：master.info记录MASTER的相关信息！ 七、启动从库同步
mysql> start slave;
mysql> show slave status＼G
Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 八、结果测试
mysql> use hitest;
mysql> select * from test; +----+--------+ | id | name | +----+--------+ | 1 | zy | | 2 | binghe | +----+--------+
2 rows in set (0.00 sec)
[root@Master-Mysql ~]# /usr/local/mysql/bin/mysql -uroot -p'' -e \ 主库建立了一个zhihu的数据库 Enter password:
[root@Slave-Mysql ~]# /usr/local/mysql/bin/mysql -uroot -p'' -e \Enter password: +------------------+
# | Database (zhihu) | +------------------+ | zhihu | +------------------+
配置Mysql数据库的主从同步(双主)
已经配置好的:
主库:192.168.199.177
从库:192.168.199.178
[root@Master-Mysql ~]# egrep \/etc/my.cnf
log-bin = /usr/local/mysql/data/mysql-bin #必须 server-id = 1 #必须 log-slave-updates #必须
auto_increment_increment = 2 #必须 auto_increment_offset = 1 #必须
slave-skip-errors = 1032,1062,1007 #非必须，建议
########################主库、从库分隔符######################## [root@Slave-Mysql data]# egrep \ #log-bin = /usr/local/mysql/data/mysql-bin server-id = 2 log-slave-updates
log-bin = /usr/local/mysql/data/mysql-bin #read-only #双主，此选项要注释掉 slave-skip-errors = 1032,1062,1007
auto_increment_increment = 2 #ID自增间隔 auto_increment_offset = 2 #ID初始位置 192.168.199.178: mysql> stop slave;
mysql> flush table with read lock; mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 120 | | | | +------------------+----------+--------------+------------------+-------------------+
mysql> system /usr/local/mywww.shanxiwang.netsql/bin/mysqldump -uroot -p'' -A -B >/tmp/192.168.199.178.sql #如果主、从一致非必须 mysql> unlock tables; #同上 mysql> system ls -l /tmp/