Mysql 的安装以及主从同步实战
2021-12-21 本文已影响0人
wudl
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 主从同步.png
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)
