配置Mysql数据库的主从同步教程详情
发布时间：2022-06-30 00:21:19 所属栏目：MySql教程 来源：互联网
导读：主从同步是实现网站分布式数据处理一个非常常用的方案了,今天我来为各位介绍配置Mysql数据库的主从同步(双主)教程,希望下文能帮助到各位哦. 配置Mysql数据库的主从同步(一主一从). 一、主库开启BINLOG、server-id [root@Master-Mysql ~]# grep -E server-id|
|
主从同步是实现网站分布式数据处理一个非常常用的方案了,今天我来为各位介绍配置Mysql数据库的主从同步(双主)教程,希望下文能帮助到各位哦. 配置Mysql数据库的主从同步(一主一从). 一、主库开启BINLOG、server-id [root@Master-Mysql ~]# grep -E "server-id|log-bin" /etc/my.cnf 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' </tmp/all.sql Warning: Using a password on the command line interface can be insecure. [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 "create database zhihu;" #主库建立了一个zhihu的数据库 Enter password: [root@Slave-Mysql ~]# /usr/local/mysql/bin/mysql -uroot -p'' -e "show databases like 'zhihu'"; Enter password: +------------------+ | Database (zhihu) | +------------------+ | zhihu | +------------------+ 配置Mysql数据库的主从同步(双主) 已经配置好的: 主库:192.168.199.177 从库:192.168.199.178 [root@Master-Mysql ~]# egrep "server-id|log-slave|log-bin|auto_increment|slave-skip-errors" /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 "server-id|log-slave|log-bin|auto_increment|slave-skip-errors|read-only" /etc/my.cnf #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/mysql/bin/mysqldump -uroot -p'' -A -B >/tmp/192.168.199.178.sql #如果主、从一致非必须 mysql> unlock tables; #同上 mysql> system ls -l /tmp/ -rw-r--r--. 1 root root 2887406 Jul 12 22:24 192.168.199.178.sql mysql> start slave; 192.168.199.177: [root@Master-Mysql ~]# /usr/local/mysql/bin/mysql -uroot -p'' < /tmp/192.168.199.178.sql #如果主、从一致非必须 mysql> update mysql.user set password=PASSWORD('admin') where user='root'; [root@Master-Mysql ~]# cat |/usr/local/mysql/bin/mysql -uroot -p'admin' <<EOF #必须 > CHANGE MASTER TO > MASTER_HOST='192.168.199.178', > MASTER_PORT=3306, > MASTER_USER='byrd', > MASTER_PASSWORD='admin', > MASTER_LOG_FILE='mysql-bin.000004', > MASTER_LOG_POS=120; > EOF mysql> start slave; mysql> show slave status＼G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.199.178 Master_User: byrd Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 938 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 1101 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Last_Errno: 0 Skip_Counter: 0 Exec_Master_Log_Pos: 938 Relay_Log_Space: 1275 Until_Condition: None Until_Log_Pos: 0 Master_SSL_Allowed: No Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_SQL_Errno: 0 Master_Server_Id: 2 Master_UUID: 34d672c3-d292-11e3-9ff5-00155dc7834c Master_Info_File: /usr/local/mysql/data/master.info SQL_Delay: 0 --phpfensi.com SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 测试:192.168.199.177: mysql> use hitest; mysql> CREATE TABLE `ces` ( -> `REL_ID` bigint(12) NOT NULL auto_increment COMMENT 'id', -> `TITLE` varchar(255) NOT NULL COMMENT 'biaoti', -> PRIMARY KEY (`REL_ID`) -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; mysql> insert into ces(TITLE) values('test'); mysql> insert into ces(TITLE) values('test'); mysql> insert into ces(TITLE) values('test'); mysql> insert into ces(TITLE) values('test25'); mysql> select * from ces; +--------+-------+ | REL_ID | TITLE | +--------+-------+ | 1 | test | | 3 | test | | 5 | test | | 25 | test25| +--------+--------+ 3 rows in set (0.03 sec) 192.168.199.178: mysql> use hitest; mysql> insert into ces(TITLE) values('test26'); mysql> insert into ces(TITLE) values('test28'); mysql> insert into ces(TITLE) values('test30'); mysql> select * from ces; +--------+--------+ | REL_ID | TITLE | +--------+--------+ | 1 | test | | 3 | test | | 5 | test | | 26 | test26 | | 28 | test28 | | 30 | test30 | +--------+--------+ 17 rows in set (0.00 sec) 说明:如果一主、一丛已经做好,只要知道从库位置点(show master status;)、然后之前主库执行(CHANGE MASTER)、之前主库开启slave(start slave)即可,其中数据库备份等步骤可以省略,如果主从有一些数据库不一致则同上操作. （编辑：航空爱好网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐