mysql主从复制搭建

实验架构：

192.168.0.92  mysql   主

192.168.0.93  mysql   从


1、环境配置请参照: https://www.cnblogs.com/effortsing/p/10367025.html

2、mysql安装 请参照：https://www.cnblogs.com/effortsing/p/9982028.html

3、Mysql主从同步环境部署


---------主服务器操作记录----------

在my.cnf文件的[mysqld]配置区域添加下面内容：

[root@master1 ~]# vim /etc/my.cnf
server-id = 1         
log-bin = mysql-bin  
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2     
auto-increment-offset = 1    
slave-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.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)


查看当前的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.00 sec)


---------------从服务器做同步操作---------------


在my.cnf文件的[mysqld]配置区域添加下面内容：

server-id = 2

重启数据库

systemctl restart mysqld



下面开始同步主数据库中的数据


先停止Slave

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)


然后连接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.01 sec)


再启动Slave

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)


查看两个线程状态是否为YES

mysql> show slave status G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes


以上表明双方已经实现了mysql主从同步。



测试主从效果


在主数据库上写入新数据

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

创建三个数据库：db1、db2、db3
  
mysql> create database db1;
Query OK, 1 row affected (0.01 sec)

mysql> create database db2;
Query OK, 1 row affected (0.01 sec)

mysql> create database db3;
Query OK, 1 row affected (0.01 sec)

给数据库授权，否则程序时无法连接db1数据库的

mysql> grant all privileges on db1.* TO 'root'@'%' identified by 'jenkins@123' with grant option;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> grant all privileges on db2.* TO 'root'@'%' identified by 'jenkins@123' with grant option;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> grant all privileges on db3.* TO 'root'@'%' identified by 'jenkins@123' with grant option;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

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.04 sec)


向db1数据库中写入数据

mysql> insert into tb1 values(2,'join');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb1 values(1,'bob');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb1;
+----+-----------+
| id | name |
+----+-----------+
| 1 | bob |
| 2 | join |
+----+-----------+
2 rows in set (0.00 sec)

  
然后在从数据库上查看，发现数据已经同步过来了!

mysql> select * from db1.tb1;
+----+-----------+
| id | name |
+----+-----------+
| 1 | bob |
| 2 | join |
+----+-----------+
2 rows in set (0.00 sec)


最后在主库上删除表，以免影响读写分离实验

mysql> drop table tb1;
Query OK, 0 rows affected (0.01 sec)


至此，Mysql主从同步环境已经实现。