文章目录
- MySQL主从同步
- 一、mysql主从同步的简单实现
- 1.环境准备
- 2.主服务器配置
- 授权用户给从服务器
- 修改配置文件
- 3.从服务器配置
- 4.验证主从
- 测试主从连接
- 读写访问测试
MySQL主从同步
大型网站为了软解大量的并发访问,除了在网站实现分布式负载均衡,还会利用主从数据库来实现读写分离,从而分担主数据库的压力。
在多个服务器上部署mysql,将其中一台认为主数据库,而其他为从数据库,实现主从同步。其中主数据库负责主动写的操作,而从数据库则只负责主动读的操作(slave从数据库仍然会被动的进行写操作,为了保持数据一致性),这样就减少数据库的连接,减轻主数据库的负载。
mysql5.7系列(一)基于centos7下安装
提示:以下是本篇文章正文内容,下面案例可供参考
一、mysql主从同步的简单实现
1.环境准备
mysql_master 192.168.236.50 master50 [已安装mysql5.7,密码:123456] 主
mysql_slave 192.168.236.51 slave51 [已安装mysql5.7,密码:123456] 从
2.主服务器配置
授权用户给从服务器
提示:我的mysql密码是123456,是已调整过MySQL密码的安全策略,具体详见mysql安装的文章
[root@master50 ~]# mysql -uroot -p123456
mysql> select user,host from mysql.user;
#查询当前Mysql用户信息
+---------------+-----------+
| user | host |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
mysql> GRANT REPLICATION SLAVE ON *.* TO 'sync'@'192.168.236.%' IDENTIFIED BY '123456';
#授权 REPLICATION SLAVE 为同步权限 *.* 指所有库中所有表 sync为用户名 192.168.236.% 可以登录的主机网段
Query OK, 0 rows affected, 1 warning (0.10 sec)
mysql> show grants for 'sync'@'192.168.236.%';
#查看权限
+----------------------------------------------------------+
| Grants for sync@192.168.236.% |
+----------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'sync'@'192.168.236.%' |
+----------------------------------------------------------+
1 row in set (0.00 sec)
#如果授权错误,删除用户即可
#mysql> delete from mysql.user where user='sync';
mysql> quit
Bye
修改配置文件
[root@slave51 ~]# vim /etc/my.cnf #修改配置文件
--------------------------------------------------
在文件内添加如下内容:
.......
#指定唯一服务ID
server-id=1
#指定二进制日志文件,开启二进制日志
log_bin=/var/lib/mysql/mysql-bin
#关闭查询缓存提高效率
query_cache_type=OFF
#指定某些库无需同步
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
binlog-ignore-db=sys
--------------------------------------------------
[root@slave51 ~]# systemctl restart mysqld #重启mysql
[root@master50 ~]# mysql -uroot -p123456
mysql> show master status;
#查看主状态
+------------------+----------+--------------+------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------------------+-------------------+
| mysql-bin.000001 | 154 | | performance_schema,mysql,sys | |
+------------------+----------+--------------+------------------------------+-------------------+
1 row in set (0.01 sec)
mysql> quit
Bye
3.从服务器配置
[root@slave51 ~]# vim /etc/my.cnf #修改配置文件
-----------------------------------------------
......
添加如下内容:
server-id=2
relay-log=/var/lib/mysql/relay-log
-----------------------------------------------
[root@slave51 ~]# systemctl restart mysqld #重启mysql服务
[root@slave51 ~]# mysql -uroot -p123456 #登录mysql
mysql> change master to master_host='192.168.236.50', master_user='sync',master_password='123456',masterr_log_file='mysql-bin.000001',master_log_pos=154;
#通过sql语句 连接主服务【注意网段及密码】
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
#启动从机制
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
#查看从状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.236.50
Master_User: sync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
.......
Exec_Master_Log_Pos: 154
Relay_Log_Space: 521
......
1 row in set (0.00 sec)
mysql> quit
Bye
4.验证主从
测试主从连接
[root@slave51 ~]# mysql -usync -p123456 -h192.168.236.50 #测试主从连接即可
mysql> quit
Bye
读写访问测试
主服务器写入:提示:在主服务器随便建一个新库 创建一个新表 插入个数据就行,这里我写了个往后文章需要用到的
[root@master50 ~]# mysql -uroot -p123456
mysql> CREATE DATABASE mysql_exercise;
#建库
Query OK, 1 row affected (0.00 sec)
mysql> USE mysql_exercise;
#指定使用该库
Database changed
mysql> CREATE TABLE Student(
-> s_id VARCHAR(20),
-> s_name VARCHAR(20) NOT NULL DEFAULT '',
-> s_birth VARCHAR(20) NOT NULL DEFAULT '',
-> s_sex VARCHAR(10) NOT NULL DEFAULT '',
-> PRIMARY KEY(s_id)
-> );
#创建表
Query OK, 0 rows affected (0.29 sec)
mysql> INSERT INTO Student VALUES('01' , 'zhangsan' , '1990-01-01' , 'man');
#往表里插入数据
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM Student;
#查看表
+------+----------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+----------+------------+-------+
| 01 | zhangsan | 1990-01-01 | man |
+------+----------+------------+-------+
1 row in set (0.00 sec)
mysql> quit
Bye
从服务器查看数据:
[root@slave51 ~]# mysql -uroot -p123456
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mysql_exercise |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> USE mysql_exercise;
Database changed
mysql> SELECT * FROM Student;
+------+----------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+----------+------------+-------+
| 01 | zhangsan | 1990-01-01 | man |
+------+----------+------------+-------+
1 row in set (0.00 sec)
mysql> quit
Bye
提示:单向主从仅支持主写主从读,如在从写,则不会同步到主!!!双向的要搭建互为主从,以后写~