mysql主从配置

1、主从简介

---

在现代企业中，数据显得尤为重要，存储数据库的工具也是五花八门，单是无论是任何数据库都存在一种隐患。

问题：

• 用一台存储数据库，数据库丢了怎么办？
• 业务量大，人多一台数据库无法保证质量怎么办？

1.2、主从作用

• 实时灾备，用于故障切换
• 读写分离，提供查询服务
• 备份，避免邮箱业务

1.3、主从形式

• 一主一从
• 主主复制
• 一主多从—扩展系统读取的性能
• 多主一从
• 联级复制

2、主从复制原理

---

主从复制步骤

• 主库将所有的写操作记录到binlog日志中并且产生一个log dump线程，将binlog日志传给从库的IO线程
• 从库分为两个线程，一个IO,一个SQL线程
  • IO线程写日志操作
  • SQL线程用于读取日志完成主从操作，最终达成一致

3、主从复制配置

---

主从复制配置步骤：

• 确保主从数据库数据一样
• 在主数据库分配账户给从数据库
• 配置主配置文件
• 配置从配置文件

开始搭建

我已经安装了mysql5.7（二进制安装）

主数据库ip	从数据库ip
192.168.0.21	192.168.0.20

#主数据库
mysql> create database wczy;
Query OK, 1 row affected (0.01 sec)
mysql> use wczy;
Database changed
mysql> create table student(id int,name varchar(10),sex varchar(2))
Query OK, 0 rows affected (0.01 sec)
mysql> insert into student values (1,'liu','m'),(2,'zhi','w'),(3,'j
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from student;
+------+------+------+
| id   | name | sex  |
+------+------+------+
|    1 | liu  | m    |
|    2 | zhi  | w    |
|    3 | jun  | m    |
+------+------+------+
3 rows in set (0.00 sec)
#备份全部数据库命令
[root@localhost ~]# mysqldump -uroot -p123 --all-databases > all_20
mysqldump: [Warning] Using a password on the command line interface
[root@localhost ~]# ls
all_2020511.sql anaconda-ks.cfg
#修改mysql配置文件/etc/my.cnf，（如果为源码安装或者yum安装就不用修改）
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql 
datadir = /opt/data 
socket = /tmp/mysql.sock 
port = 3306
log-bin=mysql-bin
server-id=1
symbolic-links=0
pid-file = /opt/data/mysql.pid 
log-error=/var/log/mysqld.log
user = mysql
skip-name-resolve
#重启数据库
[root@localhost ~]# service mysqld restart
#重新登录并且查看数据库状态
[root@localhost ~]# mysql -uroot -p"123"
mysql> show matser status;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'matser status' at line 1
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#创建用户并且授权
mysql> create user 'user1'@'192.168.0.20' identified by '123';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'user1'@'192.168.0.20';
#将备份好的数据库导入到从服务器中
[root@localhost ~]# scp all_2020511.sql 192.168.0.20:/root/

#从数据库
#将主的全局备份导入
[root@client ~]# mysql -uroot -p123 <all_2020511.sql 
[root@client ~]# mysql -uroot -p123
#查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wczy               |
+--------------------+
5 rows in set (0.00 sec)
[root@client ~]# vim /etc/my.cnf 
[root@client ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql 
datadir = /opt/data 
socket = /tmp/mysql.sock 
port = 3306
log-bin=mysql-bin
server-id=1
symbolic-links=0
pid-file = /opt/data/mysql.pid 
log-error=/var/log/mysqld.log
user = mysql
skip-name-resolve
#重启数据库
[root@client ~]# service mysqld restart
[root@client ~]# mysql -uroot -p123
#导入主数据库的相关信息
mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.0.21',
    -> MASTER_USER='user1',
    -> MASTER_PASSWORD='123',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=771;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
#查看状态
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.0.21
                  Master_User: user1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 771
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No

效果验证

#主数据库
mysql> use wczy;
mysql> insert into student values (1,'bbb','w');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+------+------+------+
| id   | name | sex  |
+------+------+------+
|    1 | liu  | m    |
|    2 | zhi  | w    |
|    3 | jun  | m    |
|    1 | bbb  | w    |
+------+------+------+

#从数据库
#重新启动
[root@client ~]# service mysqld restart;
[root@client ~]# mysql -uroot -p123
mysql> use wczy;
mysql> select * from student;
+------+------+------+
| id   | name | sex  |
+------+------+------+
|    1 | liu  | m    |
|    2 | zhi  | w    |
|    3 | jun  | m    |
|    1 | bbb  | w    |
+------+------+------+
4 rows in set (0.00 sec)

mysql>