赞
踩
实验架构:192.168.0.92mysql 主192.168.0.93mysql 从1、环境配置请参照: https://www.cnblogs.com/effortsing/p/10367025.html2、mysql安装 请参照:https://www.cnblogs.com/effortsing/p/9982028.html3、Mysql主从同步环境部署---------主服务器操作记录----------在my.cnf文件的[mysqld]配置区域添加下面内容:
[root@master1~]#vim /etc/my.cnf
server-id = 1log-bin = mysql-bin
sync_binlog= 1binlog_checksum=none
binlog_format=mixed
auto-increment-increment = 2auto-increment-offset = 1slave-skip-errors =all
重启数据库
systemctl restart mysqld
授权从服务器同步权限
mysql> grant replication slave on *.* to 'root'@'192.168.0.%' identified by 'jenkins@123';
Query OK, 0 rows affected,1 warning (0.00sec)
mysql>flush privileges;
Query OK, 0 rows affected (0.00sec)
查看当前的binlog以及数据所在位置
mysql>show master status;+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 996 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00sec)---------------从服务器做同步操作---------------在my.cnf文件的[mysqld]配置区域添加下面内容:
server-id = 2重启数据库
systemctl restart mysqld
下面开始同步主数据库中的数据
先停止Slave
mysql>stop slave;
Query OK, 0 rows affected (0.01sec)
然后连接Master
mysql> change master to master_host='192.168.0.92',master_user='root',master_password='jenkins@123',master_log_file='mysql-bin.000006',master_log_pos=150;
Query OK, 0 rows affected,2 warnings (0.01sec)
再启动Slave
mysql>start slave;
Query OK, 0 rows affected (0.01sec)
查看两个线程状态是否为YES
mysql>show slave status \G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
以上表明双方已经实现了mysql主从同步。
测试主从效果
在主数据库上写入新数据
mysql>unlock tables;
Query OK, 0 rows affected (0.00sec)
创建三个数据库:db1、db2、db3
mysql>create database db1;
Query OK,1 row affected (0.01sec)
mysql>create database db2;
Query OK,1 row affected (0.01sec)
mysql>create database db3;
Query OK,1 row affected (0.01sec)
给数据库授权,否则程序时无法连接db1数据库的
mysql> grant all privileges on db1.* TO 'root'@'%' identified by 'jenkins@123'with grant option;
Query OK, 0 rows affected,1 warning (0.01sec)
mysql> grant all privileges on db2.* TO 'root'@'%' identified by 'jenkins@123'with grant option;
Query OK, 0 rows affected,1 warning (0.01sec)
mysql> grant all privileges on db3.* TO 'root'@'%' identified by 'jenkins@123'with grant option;
Query OK, 0 rows affected,1 warning (0.01sec)
mysql>flush privileges;
Query OK, 0 rows affected (0.00sec)
db1数据库创建1个表:tb1
mysql>use db1;
Database changed
mysql> create table if not exists tb1 ( id int(10) PRIMARY KEY AUTO_INCREMENT, name varchar(50) NOT NULL);
Query OK, 0 rows affected (0.04sec)
向db1数据库中写入数据
mysql> insert into tb1 values(2,'join');
Query OK,1 row affected (0.00sec)
mysql> insert into tb1 values(1,'bob');
Query OK,1 row affected (0.00sec)
mysql> select * fromtb1;+----+-----------+
| id | name |
+----+-----------+
| 1 | bob |
| 2 | join |
+----+-----------+
2 rows in set (0.00sec)
然后在从数据库上查看,发现数据已经同步过来了!
mysql> select * fromdb1.tb1;+----+-----------+
| id | name |
+----+-----------+
| 1 | bob |
| 2 | join |
+----+-----------+
2 rows in set (0.00sec)
最后在主库上删除表,以免影响读写分离实验
mysql>drop table tb1;
Query OK, 0 rows affected (0.01sec)
至此,Mysql主从同步环境已经实现。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。