Mysql 的安装以及主从同步实战

最新推荐文章于 2024-11-08 22:59:30 发布

wudl5566

最新推荐文章于 2024-11-08 22:59:30 发布

阅读量613

点赞数 1

分类专栏：数据库文章标签： mysql 数据库 database

wudl

本文链接：https://blog.csdn.net/wudonglianga/article/details/122051580

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

1. mysql 的安装

1.1 mysql 的安装文件

rw-r--r--. 1 root root  25090196 8月   6 2020 mysql-community-client-5.7.20-1.el7.x86_64.rpm
-rw-r--r--. 1 root root    278300 8月   6 2020 mysql-community-common-5.7.20-1.el7.x86_64.rpm
-rw-r--r--. 1 root root   2238604 8月   6 2020 mysql-community-libs-5.7.20-1.el7.x86_64.rpm
-rw-r--r--. 1 root root   2115892 8月   6 2020 mysql-community-libs-compat-5.7.20-1.el7.x86_64.rpm
-rw-r--r--. 1 root root 171597916 8月   6 2020 mysql-community-server-5.7.20-1.el7.x86_64.rpm

1.2 mysql 安装

rpm -ivh mysql-community-common-5.7.20-1.el7.x86_64.rpm --nodeps --force
rpm -ivh mysql-community-libs-5.7.20-1.el7.x86_64.rpm --nodeps --force
rpm -ivh mysql-community-libs-compat-5.7.20-1.el7.x86_64.rpm  --nodeps --force
rpm -ivh mysql-community-client-5.7.20-1.el7.x86_64.rpm  --nodeps --force
rpm -ivh mysql-community-server-5.7.20-1.el7.x86_64.rpm  --nodeps --force

1.3 安装目录说明

数据库目录：/var/lib/mysql/
命令配置：/usr/share/mysql  (mysql.server命令及配置文件)
相关命令：/usr/bin   (mysqladmin mysqldump等命令)
启动脚本：/etc/rc.d/init.d/   (启动脚本文件mysql的目录)
系统配置：/etc/my.conf

1.4 启动命令

systemctl start mysqld.service
提示：
使用 service 启动：service mysqld start
使用 mysqld 脚本启动：/etc/inint.d/mysqld start
使用 safe_mysqld 启动：safe_mysqld&

1.5 停止命令

使用 service 启动：service mysqld stop
使用 mysqld 脚本启动：/etc/inint.d/mysqld stop
mysqladmin shutdown

1.6 Mysql重启命令

使用 service 启动：service mysqld restart
使用 mysqld 脚本启动：/etc/inint.d/mysqld restart

1.7 查看mysql启动后的密码：

cat /var/log/mysqld.log

1.8 设置mysql密码的安全策略

set global validate_password_policy=LOW;
set global validate_password_length=4;
注：
Mysql 因为是5.7 对密码的安全要求比较高，默认密码策略要求密码是大小写字母+数字+特殊字母的组合而且最少8位,不需要修改策略的可以跳过这步。

1.9 查看密码策略规则：

SHOW VARIABLES LIKE 'validate_password%';

1.10修改密码策略

set global validate_password.check_user_name=OFF;
set global validate_password.policy=LOW;
set global validate_password.length=4;
flush privileges;

1.11修改密码（简单的密码）

ALTER USER root@localhost IDENTIFIED BY ‘root’;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
flush privileges;

1.12 9.设置允许远程登录

(1)service mysqld restart
(2)use mysql;
11.更新权限
update user set Host='%' where User='root';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

grant all privileges on *.* to 'root'@'%' identified by 'master01@#%2021' with grant option;
flush privileges;

2. mysql 主从同步

在这里插入图片描述

MySQL之间数据复制的基础是二进制日志文件（binary log file）。
一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。

同步的步骤：

1.主库将数据库的变更操作记录到Binlog日志文件中
2.从库读取主库中的Binlog日志文件信息写入到从库的Relay Log中继日志中
3.从库读取中继日志信息在从库中进行Replay,更新从库数据信息

2.mysql 主库(Master)设置

2.1 配置文件 vi /etc/my.conf

server-id=162
log-bin=mysql-bin
sync-binlog=1
# 指定不同步的库
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
binlog-ignore-db=mysql
#指定同步的库
binlog-do-db=test

2.2 主库重启mysql

systemctl restart mysqld

2.3 主库创建用户并且授权操作

mysql> grant replication slave on *.* to 'wudl'@'%' identified by 'Admin@123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant  all privileges    on *.* to 'wudl'@'%' identified by 'Admin@123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql> 

授权说明：
GRANT 权限名字(多个权限用","号隔开) ON 数据库名.表名 TO '用户名'@'允许登录的ip地址'[IDENTIFIED BY '密码'];
 grant select,create,drop,update,alter on *.* to 'wudl'@'localhost' identified by 'Admin@123' with grant option;

2.4 查看主库的状态

mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000092 |     1417 | test         | information_schema,performance_schema,sys,mysql |                   |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)

mysql>

******************************* 从库的配置

2.5 从库的配置设置

server-id=163
#log-bin=mysql-bin
# 权限设置
relay_log=mysql-relay-bin
read_only=1

2.6重启从库 mysql

systemctl restart mysqld

2.8查看从库的状态：

mysql> show slave status;
Empty set (0.00 sec)

mysql>

2.9 从库的授权

mysql> change master to master_host='192.168.1.162' , master_port =3306 ,master_user='wudl',master_password='Admin@123',master_log_file='mysql-bin.000092',master_log_pos=1417;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql>

2.10启动从库

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

mysql>

2.11查看状态

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.162
                  Master_User: wudl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000092
          Read_Master_Log_Pos: 1417
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000092
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1417
              Relay_Log_Space: 527
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 162
                  Master_UUID: 7a81728c-d7e0-11ea-9f80-000c295020d6
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql>

说明：当两个都为yes 就说明配置成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

撤销权限

+-------------------------------------------+
| Grants for wudl@%                         |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'wudl'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> REVOKE ALL ON *.* FROM 'wudl'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GRANTS FOR 'wudl'@'%';
+----------------------------------+
| Grants for wudl@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO 'wudl'@'%' |
+----------------------------------+
1 row in set (0.00 sec)

在主库上操作test 数据库就能同步过去

重新赋值权限

mysql> grant replication slave,replication client,select,create,insert on *.* to 'wudl'@'%' identified by 'Admin@123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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