centos 安装mysql, mysql管理员命令 add user, grant, 查看包含字段

CentOS 8 安装 MySql并设置允许远程连接

一、下载mysql5.7安装包

下载地址：https://dev.mysql.com/downloads/mysql/5.7.html

二、mysql5.7安装包上传到linux服务器

安装 rz

yum install -y lrzsz

linux服务器根目录下新建文件夹：

• tools，或 /usr/local/soft/jar.back 文件夹，存放软件安装包
• /usr/local/soft 文件夹，存放安装后的软件

三、检查系统是否安装过 mysql、 MariaDB, 卸载

rpm -qa | grep mysql 
rpm -qa | grep mariadb

• 执行卸载 如有mysql 同理 卸载方法相同

rpm -e --nodeps mariadb-libs-5.5.60-1.el7-5.x86_64

• 如果系统自带mysql，查询所有mysql 对应的文件夹，全部删除

 whereis mysql
 find / -name mysql

• 删除etc目录下的my.cnf ，一定要删掉，等下再重新建，之前我将就用这个文件，后面改配置各种不生效，因为此目录下没有my.cnf文件，没有执行可命令

五、检查有无安装过mysql 用户组，没有的话创建

• 检查mysql 用户组是否存在

cat /etc/group | grep mysql
cat /etc/passwd |grep mysql

• 创建mysql 用户组和用户

groupadd mysql
useradd -r -g mysql mysql

六、安装mysql5.7步骤

• 解压安装mysql安装包到/usr/local/soft目录下

tar -zxvf mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz -C  /usr/local/soft

• 修改mysql-5.7.36-linux-glibc2.12-x86_64名称为mysql5.7

mv mysql-5.7.36-linux-glibc2.12-x86_64/ mysql5.7

• 更改mysql5.7 目录下所有文件夹所属的用户组、用户以及权限

chown -R mysql:mysql /usr/local/soft/mysql5.7
chmod -R 755 /usr/local/soft/mysql5.7

• 进入mysql5.7/bin/目录，编译安装并初始化mysql, 务必记住数据库管理员临时密码

./mysqld --initialize --user=mysql --datadir=/data/db/mysql5.7/data --basedir=/usr/local/soft/mysql5.7

编写 /etc/my.cnf，并添加配置

注意几个文件的权限： 最好改为 chown -R mysql:mysql xxx
或 chmod 755
socket = /tmp/mysql.sock
/data/db/mysql57/data
/data/db/mysql57/log/mysql-slow.log

[client]
#password       = your_password
port            = 3306
socket          = /data/db/mysql57/mysql.sock

[mysqld]
## skip-grant-tables
lower_case_table_names=1
port            = 3306
socket          = /data/db/mysql57/mysql.sock
datadir = /data/db/mysql57/data
#pid-file=/var/run/mysqld/mysqld.pid
pid-file=/data/db/mysql57/mysqld/mysqld.pid
# skip-name-resolve

## 出错时，提示具体错误信息msg，不只code
lc-messages-dir=/usr/local/soft/mysql57/share/english
default_storage_engine = InnoDB
performance_schema_max_table_instances = 400
table_definition_cache = 400
skip-external-locking
key_buffer_size = 64M
max_allowed_packet = 100G
table_open_cache = 256
sort_buffer_size = 1M
net_buffer_length = 4K
read_buffer_size = 1M
read_rnd_buffer_size = 256K
myisam_sort_buffer_size = 16M
thread_cache_size = 32
query_cache_size = 32M
tmp_table_size = 64M
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

explicit_defaults_for_timestamp = true
#skip-name-resolve
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535

##### log: binlog 
# 可以通过以下这些属性指定需要针对哪些库或者哪 些表记录binlog
# 需要同步的二进制数据库名 
# binlog-do-db=testdemo
server-id=312
# gtid主从模式用row 
# binlog_format=mixed
log-bin=/data/db/mysql57/log/mysql-bin.log  #默认路径可修改
# 指定错误日志 帮助排查错误信息
log-error=/data/db/mysql57/log/error.log
binlog_do_db=ltkj-personnel-manager

expire_logs_days = 10    #日志过期时间，设置为0则永不过期
max_binlog_size=100M         #超过max_binlog_size或超过6小时会切换到下一序号文件
long_query_time=3
binlog_cache_size=16M           #二进制日志缓冲大小,通过show status like 'binlog_%';查看调整写入磁盘的次数，写入磁盘为0最好max_binlog
sync_binlog= 1            #二进制日志（binary log）同步到磁盘的频率
#
#log_queries_not_using_indexes=on

relay_log_recovery  = 1            #当slave从库宕机后，假如relay-log损坏了，
#导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，
##并且重新从master上获取日志，这样就保证了relay-log的完整性。

early-plugin-load = ""

slow_query_log=1
slow-query-log-file=/data/db/mysql57/log/mysql-slow.log


character-set-server=utf8mb4
innodb_data_home_dir = /data/db/mysql57/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data/db/mysql57/data
innodb_buffer_pool_size = 256M
innodb_log_file_size = 128M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 1 #每次事务提交将日志缓冲区写入log file，并同时flush到磁盘。
innodb_lock_wait_timeout = 50
innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 2
innodb_write_io_threads = 2

# # -- 主从同步 start --------------
# gtid-mode=on                              #开启gtid模式
# enforce-gtid-consistency=on       #强制gtid一致性，开启后对特定的create table不支持
# binlog-format=row
# log-slave-updates=1                    #从库binlog记录主库同步的操作日志
# skip-slave-start=1                        #跳过slave复制线程
# #不用备份的数据库 
# binlog-ignore-db=information_schema
# binlog-ignore-db=performation_schema
# binlog-ignore-db=sys
# binlog-ignore-db=mysql
# #binlog-ignore-db=mysql
# #
# # -- 主从同步 end --------------

[mysqldump]
quick
max_allowed_packet = 500M
user=root
password="lk.com"

[mysql]
no-auto-rehash
## lower_case_table_names=1

[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 1M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

• 授予my.cnf 配置文件775权限，如下：

chmod -R 775 /etc/my.cnf

• 启动mysql 服务器

1）、查询服务

ps -ef | grep mysql

ps -ef | grep mysqld

七、添加 systemctl 命令单元

https://my.oschina.net/u/4984817/blog/5543878

环境变量，后面有创建软连接方式
创建软连接、为了避免在使用 MySQL 时总是输入客户端程序的路径名，将/usr/local/mysql/bin 目录添加到PATH变量中
vim /etc/bashrc
在文件头部加上如下代码后
保存退出

vim /etc/bashrc

export PATH=$PATH:/usr/local/mysql/bin 

# 刷新文件，使配置生效
source /etc/bashrc

或者：

ln -s /usr/local/soft/mysql57/bin/mysql    /usr/bin

重启下mysql服务

systemctl restart mysqld.service

查看mysql服务状态

systemctl status mysqld.service

查看下默认的临时密码，我的密码是： >dgp,9q&?ikW

grep 'temporary passwod' /var/log/mysqld.log

测试登录

mysql -u root -p

然后修改密码

ALTER USER 'root'@'localhost' IDENTIFIED BY '~1QAZxsw2'

修改允许远程连接

use mysql;
update user set Host = '%' where Host = 'localhost' and User='root';
flush privileges;

开放3306端口

firewall-cmd --zone=public --add-port=3306/tcp --permanent

重启防火墙

firewall-cmd --reload

查看防火墙状态 显示success 即成功开启

systemctl status firewalld

查看指定端口状态 显示 success 即端口成功开启

firewall-cmd --query-port=6379/tcp

配合 my.cnf 之后使用数据库连接工具即可连接成功；

========= 防火墙操作 开放指定端口 ===============

4. 开启端口

zone -- 作用域
add-port=80/tcp -- 添加端口，格式为：端口/通讯协议
permanent -- 永久生效，没有此参数重启后失效

firewall-cmd --zone=public --add-port=3306/tcp --permanent

开启3306端口后，配合my.cnf, workbench或naivcat 就能连接到MySQL数据库了

5. 重启防火墙

firewall-cmd --reload

6. 常用命令介绍

1.firewalld的基本使用

启动： systemctl start firewalld
查状态：systemctl status firewalld
停止： systemctl disable firewalld
禁用： systemctl stop firewalld
在开机时启用一个服务：systemctl enable firewalld.service
在开机时禁用一个服务：systemctl disable firewalld.service
查看服务是否开机启动：systemctl is-enabled firewalld.service
查看已启动的服务列表：systemctl list-unit-files|grep enabled
查看启动失败的服务列表：systemctl --failed

2.配置firewalld-cmd

查看版本： firewall-cmd --version
查看帮助： firewall-cmd --help
显示状态： firewall-cmd --state
查看所有打开的端口： firewall-cmd --zone=public --list-ports
更新防火墙规则： firewall-cmd --reload
查看区域信息: firewall-cmd --get-active-zones
查看指定接口所属区域： firewall-cmd --get-zone-of-interface=eth0
拒绝所有包：firewall-cmd --panic-on
取消拒绝状态： firewall-cmd --panic-off
查看是否拒绝： firewall-cmd --query-panic

3.那怎么开启一个端口呢

添加
firewall-cmd --zone=public(作用域) --add-port=80/tcp(端口和访问类型) --permanent(永久生效)
firewall-cmd --zone=public --add-service=http --permanent
firewall-cmd --reload # 重新载入，更新防火墙规则
firewall-cmd --zone= public --query-port=80/tcp #查看

删除

firewall-cmd --zone= public --remove-port=80/tcp --permanent # 删除

服务操作

firewall-cmd --list-services
firewall-cmd --get-services
firewall-cmd --add-service=<service>
firewall-cmd --delete-service=<service>
在每次修改端口和服务后/etc/firewalld/zones/public.xml文件就会被修改,所以也可以在文件中之间修改,然后重新加载
使用命令实际也是在修改文件，需要重新加载才能生效。

4.详细使用

firewall-cmd --permanent --zone=public --add-rich-rule='rule family="ipv4" source address="192.168.0.4/24" service name="http" accept' //设置某个ip访问某个服务
firewall-cmd --permanent --zone=public --remove-rich-rule='rule family="ipv4" source address="192.168.0.4/24" service name="http" accept' //删除配置
firewall-cmd --permanent --add-rich-rule 'rule family=ipv4 source address=192.168.0.1/2 port port=80 protocol=tcp accept' //设置某个ip访问某个端口
firewall-cmd --permanent --remove-rich-rule 'rule family=ipv4 source address=192.168.0.1/2 port port=80 protocol=tcp accept' //删除配置

firewall-cmd --query-masquerade # 检查是否允许伪装IP
firewall-cmd --add-masquerade # 允许防火墙伪装IP
firewall-cmd --remove-masquerade # 禁止防火墙伪装IP

firewall-cmd --add-forward-port=port=80:proto=tcp:toport=8080 # 将80端口的流量转发至8080
firewall-cmd --add-forward-port=proto=80:proto=tcp:toaddr=192.168.1.0.1 # 将80端口的流量转发至192.168.0.1
firewall-cmd --add-forward-port=proto=80:proto=tcp:toaddr=192.168.0.1:toport=8080 # 将80端口的流量转发至192.168.0.1的8080端口

5.示例

先查看防火墙是否开启了1521端口：
firewall-cmd --permanent --query-port=1521/tcp
打印结果如下：
no
表示没有开放1521端口，那么添加下该端口：
firewall-cmd --permanent --add-port=1521/tcp
打印结果如下：
success
重新加载防火墙策略：
firewall-cmd --reload
执行成功后，查看1521端口是否被开启：
firewall-cmd --permanent --query-port=1521/tcp
打印结果如下：
yes

八、别人想连接我windows电脑的vmware里的 mysql

https://my.oschina.net/u/4984817/blog/5611658

更多命令，使用 firewall-cmd --help 查看帮助文件

======== mysql 管理命令================

1.创建用户

主机名中 ip表示只允许某个ip访问 ，%表示所有，localhost表示只允许本地访问

注意某个用户的信息中 用户名和主机名组成了一个用户，同一个用户名不同的主机名组成的是不同的用户

例

1. 使用root用户登录mysql
create user 'mall'@'%' identified by 'mall.test';
flush privileges; 

	可能会遇到密码强度问题：
	mysql修改密码报错:Your password does not satisfy the current policy requirements
	解决办法：
	
	1-1、查看 mysql 初始的密码策略，
	输入语句 “ SHOW VARIABLES LIKE ‘validate_password%’; ” 进行查看，
	
	1-2、首先需要设置密码的验证强度等级，设置 validate_password_policy 的全局参数为 LOW 即可，
	输入设值语句 “ set global validate_password_policy=LOW; ” 进行设值，
	
	1-3、当前密码长度为 8 ，如果不介意的话就不用修改了，按照通用的来讲，设置为 6 位的密码，设置 validate_password_length 的全局参数为 6 即可，
	输入设值语句 “ set global validate_password_length=6; ” 进行设值，
	
	1-4、现在可以为 mysql 设置简单密码了，只要满足六位的长度即可，
	输入修改语句 “ ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘123456’; ” 可以看到修改成功，表示密码策略修改成功了！！！

	注：在默认密码的长度最小值为 4 ，由 大/小写字母各一个 + 阿拉伯数字一个 + 特殊字符一个，
	只要设置密码的长度小于 3 ，都将自动设值为 4 ，
	
	关于 mysql 密码策略相关参数；
	1）、validate_password_length 固定密码的总长度；
	2）、validate_password_dictionary_file 指定密码验证的文件路径；
	3）、validate_password_mixed_case_count 整个密码中至少要包含大/小写字母的总个数；
	4）、validate_password_number_count 整个密码中至少要包含阿拉伯数字的个数；
	5）、validate_password_policy 指定密码的强度验证等级，默认为 MEDIUM；
	
	关于 validate_password_policy 的取值：
	0/LOW：只验证长度；
	1/MEDIUM：验证长度、数字、大小写、特殊字符；
	2/STRONG：验证长度、数字、大小写、特殊字符、字典文件；


2. 删除用户  
delete from mysql.user WHERE host=’hostname’ AND user=’username’;
flush privileges; 

由于直接对user表操作，执行完命令之后，需要使用
FLUSH PRIVILEGES 语句，重新加载用户权限

------------------------------------------------------------------------
3、为新用户分配远程权限，可以指定数据库dbname和表名，可以用*替指所有。

    grant all privileges on `mall_auth`.* to 'mall'@'%' identified by 'mall.test';  

    分配好之后之后记得执行下面指令更新权限：
	flush privileges; 

注：如果报错 #1046 或 #1064

————————————————

1-2.修改用户名

例：把用户名root修改为xiaoming

update `mysql`.`user` set user="xiaoming" where user="root";

flush privileges;

1-3.修改密码

alter user 'xiaoming'@'localhost' identified by '123456';

flush privileges;

在这里,我决定创建一个mysql用户“bird”,我想只为db“bird_test”的特定表提供访问权限.

那么,我可以知道如何为MYSQL中的用户授予特定的表perm吗？

解决方法:

首先 上面创建了用户以后。
再授权：

GRANT USAGE ON *.* TO bird@'localhost' IDENTIFIED BY 'whateverpassword';

仅授予对bird_test数据库中siteindex表的SELECT权限

GRANT SELECT ON bird_test.siteindex TO bird@'localhost';

仅授予对bird_test数据库中whois1表的SELECT权限

GRANT SELECT ON bird_test.whois1 TO bird@'localhost';

授予bird_test数据库中每个表的所有权限

GRANT ALL PRIVILEGES ON bird_test.* to 'bird'@'%' identified by 'bird.test'; 

可能报错，没有xx-xx-表，用撇号 括起来，注意不是单引号

撤销用户权限 REVOKE

用户对某个数据库的全部权限

mysql> REVOKE ALL PRIVILEGES ON database_name.* FROM 'user_name'@'host';

注：关键字 privileges 可以省略。

撤销用户对某个数据库的部分权限
例如，收回用户对某个数据库的插入、更新、删除的权限：

mysql> REVOKE INSERT, UPDATE, DELETE ON database_name.* FROM 'user_name'@'host';

撤销用户对某个数据库的某张表的部分权限
例如，收回用户对某个数据库的某个表的插入、更新、删除的权限：

mysql> REVOKE INSERT, UPDATE, DELETE ON database_name.table_name FROM 'user_name'@'host';

撤销用户对任何数据库的任何表的部分权限
例如，收回用户查询表数据的权限（注：任何数据库的任何表都不能查询）：

mysql> REVOKE SELECT ON *.* FROM 'user_name'@'host'; 

撤销 WITH GRANT OPTION 权限
必须使用下面的语句才能撤销 WITH GRANT OPTION 权限：

mysql> revoke all privileges,grant option from 'user_name'@'host';

注：关键词 privileges 可以省略掉。

权限表

撤销权限的巨坑

撤销语句要和授权语句完全一致

假如你给用户 pig@% 授权的语句如下：

mysql> GRANT SELECT ON test.user TO 'pig'@'%';

那么使用如下的语句，根本无法撤销上述的查询权限（即不能撤销该用户对 test 数据库中 user 表的 SELECT 操作权限）：

mysql> REVOKE SELECT ON *.* FROM 'pig'@'%';

而且使用下面的语句，还会报错：

mysql> REVOKE SELECT ON test.* FROM 'pig'@'%';
ERROR 1141 (42000): There is no such grant defined for user 'pig' on host '%'

同样地，如果你使用下面的语句给用户 pig@% 授权：

GRANT SELECT ON *.* TO 'pig'@'%';

那么使用下面的语句，根本无法撤销该用户对 test 数据库中 user 表的 SELECT 权限：

REVOKE SELECT ON test.user FROM 'pig'@'%';

注： USAGE 权限是无法撤销的

———————————————

授权：

GRANT ALL PRIVILEGES ON bird_test.* TO 'birdtest'@'%';
flush PRIVILEGES;

show grants for 'birdtest';

当

REVOKE ALL PRIVILEGES ON *.* FROM 'birdtest'@'%';
flush privileges;

show grants for 'birdtest';
或  show grants for 'birdtest'@'%'; 
结果为： GRANT USAGE ON *.* TO 'birdtest'@'%'
时

只剩USAGE，意味着无权限。

---

mysql 查询哪些表中含有 xx字段

SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('space_id')
AND TABLE_SCHEMA='bird'  ;

九、基于MySQL GTID搭建主从

什么是MySQL GTID ？

MySQL GTID（Global Transaction Identifier）是MySQL
5.6版本引入的一种全局事务标识符，用于唯一标识一个事务，并确保事务在主从复制中的一致性。

原理

GTID的原理是通过在主库上为每个事务分配一个全局唯一的标识符。每个GTID由以下三个部分组成：源服务器的唯一标识符（server_uuid）、事务序号（transaction_id），以及集群名称（gtid_domain_id）。这个标识符会随着事务的提交被写入二进制日志，并被主库和从库的服务器记录和追踪。

GTID的作用：

主从切换：使用GTID可以简化主从切换的过程，从库可以通过GTID自动找到上一次复制的位置，从而无需手动指定二进制日志文件和偏移量。

数据一致性：GTID保证了主从复制的数据一致性。主库上的每个事务都被唯一标识，从库会根据GTID进行复制，避免了重复复制或跳过复制的情况。

简化拓扑：使用GTID，可以更轻松地改变主从复制的拓扑结构，比如添加或移除从库，而无需重新设置复制位置。

搭建过程

1.准备两台虚拟机，自行规划谁是主，谁是从，并安装mysql （此处省略）

2. 在主库创建一个只用于同步的账号，授权给从数据库使用
   首先登陆mysql，然后创建一个用户名为sync，密码为xxxx的账户，该账户可以被192.168.253网段下的所有ip地址使用，且该账户只能进行主从同步

grant replication slave on *.* to 'sync'@'%' identified by 'ltkj.sync';
## grant replication slave on *.* to ‘sync’@‘192.168.253.%’ identified by ‘123456’;
flush privileges;

3. 修改主库my.cnf配置文件

log-bin=/data/db/mysql57/log/mysql-bin.log     #开启二进制日志
server-id=11                                 #服务器ID，必须唯一
gtid-mode=on                              #开启gtid模式
enforce-gtid-consistency=on       #强制gtid一致性，开启后对特定的create table不支持
binlog-format=row                       #默认为mixed混合模式，更改成row复制，为了数据一致性
log-slave-updates=1                    #从库binlog记录主库同步的操作日志
skip-slave-start=1                        #跳过slave复制线程
binlog-ignore-db=mysql
#lower_case_table_names=1

4. 重启主库的mysql

systemctl restart mysqld

5. 查看mysql状态

• 查看主库gtid开启状态

  mysql> show variables like "%GTID%";
  

  

• 查看主库gtid开启状态

  mysql> show global variables like "%log_%";
  

6. 配置从库的my.cnf文件

[client]
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock

[mysqld]
## skip-grant-tables
lower_case_table_names=1
port            = 3306
socket          = /tmp/mysql.sock
datadir = /data/db/mysql57/data
lc-messages-dir=/usr/local/soft/mysql57/share/english
symbolic-links=0


pid-file=/var/run/mysqld/mysqld.pid
skip-name-resolve

log-error=/data/db/mysql57/log/mysqld.log
log-bin=mysql-bin #启用二进制日志，将日志文件保存为 mysql-bin
binlog_format=mixed

server-id=214                    # ip 末位 
gtid-mode=on  #开启gtid
enforce-gtid-consistency=on #强制要求在复制过程中严格遵守GTID的一致性，确保数据一致性。     
binlog-format=row #设置二进制日志的格式为行级别的格式，以记录每个事务中具体执行的行变更。
log-slave-updates=1 #允许从服务器将接收到的更新事务写入自身的二进制日志文件，用于级联复制中的中继服务器。
skip-slave-start=1                        #跳过slave复制线程
#lower_case_table_names=1
relay-log =/data/db/mysql57/log/slave-relay-bin.log
 
relay-log-index=/data/db/mysql57/log/slave-relay-bin.index

## 注意这里：如果从库 想用别的库名 personnel-manager -> asset-manager 
## 如果主从库名相同，用这个
#replicate-do-db = 数据库名
## 如果主从库名不同
#replicate-rewrite-db = 主数据库名 -> 从数据库名
 
#如果同步部分库表
#replicate-wild-do-table = 数据库名.表1
#replicate-wild-do-table = 数据库名.表2
 
#忽略某表
#Replicate_Wild_Ignore_Table = 数据库名.表1

## 忽略xx表   选择使用
# replicate-wild-ignore-table=mysql.*
# replicate-wild-ignore-table=sys.*

6-2. 指定部分表

基于GTID主从复制 replication_do_table
replication_do_table是MySQL中的一个高级复制功能，它允许你指定只有在特定表上的变更才能被复制到从服务器。这个功能通常与基于GTID的复制一起使用。

要使用replication_do_table，你需要在从服务器上设置一个复制规则，指定哪些表应该被复制。这可以通过在从服务器的my.cnf配置文件中使用replicate_do_table选项来实现。

以下是一个配置示例，它指定只有数据库为mydb和yourdb中的表mytable和yourtable的变更才会被复制：

[mysqld]
# 其他复制相关配置
 
# 只复制指定数据库和表
replicate_do_table=mydb.mytable
replicate_do_table=mydb.yourtable
replicate_do_table=yourdb.mytable
replicate_do_table=yourdb.yourtable
在配置了从服务器后，你需要重启MySQL服务来使配置生效，并且要清除复制状态（如果之前配置了不同的复制规则）：

STOP SLAVE;
RESET SLAVE;
START SLAVE;
确保在配置replicate_do_table之前，所有的从服务器已经清除并且重新初始化了复制状态。一旦配置生效，只有指定的表将会被复制到从服务器。

6-3 忽略某些表

 Replicate_Do_DB: 
          Replicate_Ignore_DB= 
           Replicate_Do_Table=
       Replicate_Ignore_Table=
      Replicate_Wild_Do_Table= 
  Replicate_Wild_Ignore_Table= 

7. 重启从库的mysql

systemctl restart mysqld

8. 查看gtid的状态

mysql> show variables like "%GTID%";

9. 开启主从同步(下面的命令是在从库上执行)

mysql> stop slave;


mysql> change master to
     master_host='192.168.21.3',
     master_user='sync',
     master_password='ltkj.sync',
     master_auto_position=1;
mysql> start slave;  

使用GTID方式同步的时候，必须指定master_auto_position=1
GTID使用master_auto_position=1代替了基于binlog和position号的主从同步方式,更便于主从同步的搭建

mysql> show slave status\G

看见两个yes，则表示搭建成功（如果显示为no，确保命令是在从库执行，并且配置了防火墙规则，或者之间关闭防火墙）

可能存在问题：在mysql主从同步的过程中检查主从同步状态时IO线程报错

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

解决：从报错信息发现是mysql的server_uuid的原因
1、检查两个mysql实例的uuid发现主从的uuid一模一样

# 使用如下命令查看了一下server_id

mysql> show variables like 'server_uuid';

+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | 81f4d8ee-bf49-11ee-8abd-fefcfe6aba43 |
+---------------+--------------------------------------+
1 row in set (0.00 sec)

2、找到从库的auto.cnf文件，修改uuid，并且重启从库实例问题解决

vim /data/db/mysql57/data/auto.cnf

systemctl restart mysql 

mysql> show variables like 'server_uuid';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    4
Current database: *** NONE ***

+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | 81f4d8ee-bf49-11ee-8abd-fefcfe6ab214 |
+---------------+--------------------------------------+
1 row in set (0.00 sec)

# 检查从库复制状态：
show slave status\G;

如果需要

可以手动为主服务器设置一个不同的UUID，方法是编辑my.cnf或my.ini文件，添加或修改server-uuid选项，并提供一个唯一的UUID值。

重新配置复制设置，并启动复制过程。

参见9.

mysql> stop slave;


mysql> change master to
     master_host='192.168.21.3',
     master_user='sync',
     master_password='ltxx.sync',
     master_auto_position=1,
     MASTER_DELAY=7200 ;  # 延迟 2小时

mysql> start slave;  

如果在已建好的主从架构上修改 延迟复制时间：

show slave status ;

stop slave ;
change master to master_delay = 7200 ;

start slave ;

show slave status ;

主从正常

主：

从：

顺便查看对应的日志：

[root@localhost ~]# tail -f /data/db/mysql57/log/error.log

2024-12-02T05:45:59.231695Z 50 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'mysql-bin.000006' at position 826905, relay log '/data/db/mysql57/log/slave-relay-bin.000003' position: 4
2024-12-02T05:50:55.026254Z 50 [Note] Error reading relay log event for channel '': slave SQL thread was killed
2024-12-02T05:50:55.026298Z 50 [Note] Slave SQL thread for channel '' exiting, replication stopped in log 'mysql-bin.000006' at position 20451670
2024-12-02T05:50:55.027806Z 49 [Note] Slave I/O thread killed while reading event for channel ''
2024-12-02T05:50:55.027826Z 49 [Note] Slave I/O thread exiting for channel '', read up to log 'mysql-bin.000006', position 20451670

————————————————

十、 手动使用主从同步来备份

把主服务器的数据复制到从服务器上，然后备份从服务器的数据，在数据量不是很大的时候使用mysqldump命令，对于很大的数据库，就直接备份数据文件。

3.1 使用mysqldump来备份
步骤：（以下的所有操作都在从服务器上进行）

1.首先暂停从服务器的复制进程

shell > mysqladmin stop-slave

或者只是暂停SQL进程（从服务器仍然能接收二进制日志的事件，但不会执行这些事件，这样能在重启SQL进程时加快复制进度）

shell > mysql -e ‘stop slave sql_thread;’

2.使用mysqldump导出全部或部分的数据库

shell > mysqldump --all-databases > fulldb.dump

3.在导出数据库后，重启复制进程

shell > mysqladmin start-slave

3.2 备份原始文件
为了保证数据文件的完整性，在备份之前首先关闭从服务器，步骤：

1.关闭从服务器：

shell > mysqladmin shutdown

2.复制数据文件，可以使用压缩命令，假如当前目录就是数据库的数据目录（在my.cnf文件中的配置项datadir的值就是该目录的位置）

shell > tar cf /tmp/dbbackup.tar ./data

3.然后再启动mysql服务器

十一、生产环境数据备份:

三种常用MySQL逻辑备份工具有：

1. （自带）mysqldump，单线程工具，MySQL官方备份工具，特点是备份速度慢，命令和操作简单。

2. （可忽略）mydumper，多线程工具，开源备份工具，特点是可手动指定备份线程数，备份速度较之mysqldump快，命令和操作稍显复杂。

3. （5.7后）mysqlshell，多线程工具，MySQL官方推荐的一款快速备份恢复工具，工具中集成了备份函数，该工具需单独部署才可使用。特点是备份速度较之mydumper又有提升，命令和操作简洁，推荐在MySQL8.0环境中使用。

参考命令

(1)  mysqldump
# 1.mysqldump备份表级tables

# mysqldump -uroot -proot -h127.0.0.1 -P3306 --single-transaction --master-data=2 --routines --events --triggers --tables table1,table2 | gzip > `date +%Y%m%d`.sql.gz

# 2.mysqldump备份库级schemas

# mysqldump -uroot -proot -h127.0.0.1 -P3306 --single-transaction --master-data=2 --routines --events --triggers --databases schema1,schema2 | gzip > `date +%Y%m%d`.sql.gz

# 3. 按表名前缀
mysqldump -uroot -p 库名 $(mysql -uroot -p 库名 -Bse "show tables like '表前缀_%'") > "导出位置.sql"

mysqldump -h 127.0.0.1 -u root -p密码 --opt --default-character-set=utf8-mb4   --hex-blob 库名 $(mysql -uroot -p  库名 -Bse "show tables like 'ims_yidu_%'") --skip-triggers  > /data/666.sql

# 3-2 按表名前缀


# 4.mysqldump恢复数据
# 因备份出来的是SQL执行语句，表级、库级和实例级对象的逻辑备份恢复数据命令是一致的，示例命令如下：
mysql -uroot -proot -h127.0.0.1 -P3306 < 20231118.sql

——————————————————————————————————————————————

3-2 第二种：按表名前缀

使用mysqldump导出指定表前缀的数据
在使用MySQL数据库时，有时我们需要备份导出数据库中以特定表前缀开头的所有表的数据。这时可以使用mysqldump命令来实现。mysqldump是MySQL提供的一个用于备份数据库的命令行工具，可以将数据库中的表结构和数据导出到一个文件中。

使用mysqldump导出指定表前缀的数据
我们可以通过在mysqldump命令中使用–tables参数来指定要导出的表，结合通配符*来实现导出指定表前缀的数据。下面是具体的示例代码：

mysqldump -u username -p database_name --tables "$(mysql -u username -p -N information_schema -e "SELECT table_name FROM tables WHERE table_schema='database_name' AND table_name LIKE 'prefix_%';")" > backup.sql

SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND ( table_name like 'ltkj_%' or table_name like 'sys_%') 
and table_schema = 'ltkj-personnel-manager'
ORDER BY table_schema, table_name;

SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND ( table_name not like 'ltkj_%' and table_name not like 'sys_%') 
and table_schema = 'ltkj-personnel-manager'
ORDER BY table_schema, table_name;

上面的命令中，我们首先使用mysql命令从information_schema数据库中获取所有以指定前缀开头的表名，然后将这些表名作为参数传递给mysqldump命令，从而导出这些表的数据到backup.sql文件中。

（2）mydumper 逻辑备份工具讲解
忽略。

——————————————————————————————————————————————
（3）逻辑备份工具mysqlshell讲解
1.mysqlshell备份表级tables

mysqlsh root@127.0.0.1:3306 -- util dump-tables schema1 'table1,table2' --output-url='/data/backup/' --threads=4 --consistent=false --dryRun=true

2.mysqlshell备份schemas

mysqlsh root@127.0.0.1:3306 -- util dump-schemas 'schema1,schema2' --output-url='/data/backup/' --threads=4 --consistent=false --dryRun=true

3.mysqlshell备份instance

mysqlsh root@127.0.0.1:3306 -- util dump-instance '/data/backup/' --threads=4 --consistent=false --dryRun=true

参数介绍：

--util dump-tables：使用MySQL Shell的util工具，并选择dump-tables子命令来导出数据表。

--output-url='/data/backup/'：指定导出文件的输出路径和前缀。

--threads=4：指定使用的线程数。

--consistent=false：禁用一致性读取，即在导出期间不使用事务。

--dryRun=true：模拟导出操作，而不实际执行。

--consistent: [ true | false ] 是否开启一执性快照备份，默认true。开启时，通过flush tables with read lock获取全局读锁，每个备份线程设置会话隔离级别RR，开启一致性快照事务。当所有线程开启事务后，获取实例级备份锁（lock intance for backup），释放全局读锁。

4.mysqlshell 恢复表级tables

备份schema1库table1/table2的数据，恢复至schema2库。

mysqlsh root@127.0.0.1:3306 -- util load-dump '/data/backup/' --analyzeTables=on --skipBinlog=true --updateGtidSet=off --schema=schema2 --dryRun=false

5.恢复dump-schemas/dump-instance备份数据

mysqlsh root@127.0.0.1:3306 -- util load-dump '/data/backup/' --analyzeTables=on --skipBinlog=true --updateGtidSet=off --dryRun=true

参数介绍：

--dryRun=true：模拟导出操作，而不实际执行

--analyzeTables=on：在加载数据后对表进行分析

--skipBinlog=true：跳过写入二进制日志。

--updateGtidSet=off：禁用更新GTID。

1. 备份

指定表名前缀备份时，可能用到

mysqldump --defaults-extra-file=/etc/my.cnf --opt --default-character-set=utf8   --hex-blob 库名 $(mysql --defaults-file=/etc/my.cnf ltkj-personnel-manager -Bse "show tables like 'ltkj_%'") > /data/666.sql

/etc/my.cnf

[client]
user=root
password=your_password

确保将your_password替换为您的实际MySQL root用户密码。
然后，您可以使用以下命令登录MySQL，而不需要在命令行中显示密码：

[root@m sh]# mysql --defaults-file=/etc/my.cnf

#!/bin/bash

# 数据库连接信息
DB_HOST="127.0.0.1"
DB_PORT="3306"
DB_USER="root"
mysql_charset="utf8mb4" #MySQL编码
DB_PASSWD="xxxx"

# 时间格式化,如 20211216
DATE="`date +%Y%m%d%H%M`"


# 要备份的数据库名称，多个用空格分开隔开 如("db1" "db2" "db3")
# 备份文件目录，末尾请不要带"/"
DIR_BACKUP="/data/back/mysql"
# 数据目录: ${DRI_BACKUP/data
DIR_DATA="${DIR_BACKUP}/data"
# 日志目录: ${HOME}/data/db-backup/logs
DIR_LOG="${DIR_BACKUP}/logs"
# 日志文件: /data/back/mysql/data/logs/db_backup.INFO.2021-12-30.log
FILE_LOG="${DIR_LOG}/db_backup.INFO.`date +%F`.log"


# 是否删除旧的备份 ON为开启 OFF为关闭
EXPIRE_BACKUP="ON"
# 文件保留天数 默认为3天，此项只有在EXPIRE_BACKUP开启时有效
expire_days=15


# 欢迎语
welcome_msg="Welcome to use MySQL backup tools!"

# 判断mysql 是否启动, mysql 没有启动则备份退出
mysql_ps=`ps -ef |grep mysql |wc -l`
mysql_listen=`netstat -an |grep LISTEN |grep $DB_PORT|wc -l`
if [ [$mysql_ps == 0] -o [$mysql_listen == 0] ]; then
        echo "ERROR: MySQL is not running! backup stop!" | tee -a ${FILE_LOG}
        exit
else
        echo $welcome_msg | tee -a ${FILE_LOG}
fi

# 目录不存在则自动创建
if [ ! -d ${DIR_LOG} ]; then
    mkdir -p ${DIR_LOG};
fi

echo -e "\n-----------------" ${DIR_LOG}
# ------------------- 2024-03-22_16:40:48 Start -------------------
echo -e "\n================= $(date +%F\ %T) Start =================" |tee -a ${FILE_LOG}

# 遍历数据库名
for database in ${DATABASES[@]};
    do
            # 打印备份的数据库名
        echo "----------- Current backup database: [ ${database} ] ------------" |tee -a ${FILE_LOG}
        echo "-------------- Backed-up database: [ ${database} ] --------------" |tee -a ${FILE_LOG}
done


# 备份指定的数据库
# mysqldump --opt --single-transaction --master-data=2 --default-character-set=utf8mb4 -h${DB_HOST} -P${DB_PORT} -u${DB_USER} -p${DB_PASSWD} -B ${DATABASES} | gzi
p > ${DIR_DATA}/mysql_backup_${DATE}.sql.gz

# mysqldump --defaults-extra-file=/etc/my.cnf -B ltkj-personnel-manager > ${DIR_DATA}/mysql_bk_${DATE}.sql
mysqldump --defaults-extra-file=/etc/my.cnf -B ltkj-personnel-manager | gzip > ${DIR_DATA}/mysql_bk_${DATE}.sql.gz

# # 备份全部数据库
# mysqldump --opt --single-transaction --master-data=2 --default-character-set=utf8mb4 -h${DB_HOST} -P${DB_PORT} -u${DB_USER} -p${DB_PASSWD} -A | gzip > ${DIR_DAT
A}/mysql_bk_${DATE}.sql.gz


echo "------- Backup file created: [ mysql_bk_${DATE}.sql.gz ]" |tee -a ${FILE_LOG}

  # ---------------------------------------------------------------------------------

# 如果开启了删除过期备份，则进行删除操作
if [ "$EXPIRE_BACKUP" == "ON" -a  "$DIR_DATA" != "" ];then
         #`find $backup_location/ -type d -o -type f -ctime +$expire_days -exec rm -rf {} ;`
         `find ${DIR_DATA}/ -mtime +$expire_days -name "mysql_bk_*" | xargs rm -rf`

         echo "expired files  delete complete! [${DIR_DATA}/'mysql_bk_*']"  |tee -a ${FILE_LOG}
         echo "expired logs  delete complete! [${DIR_LOG}/*.log]"  |tee -a ${FILE_LOG}
fi

echo -e  "All database backup success! Thank you !" | tee -a ${FILE_LOG}
echo -e "\n >>>>>> $(date +%F\ %T) End <<<<<< " |tee -a ${FILE_LOG}
exit

2. 恢复：

远程复制 scp 指定端口号

sudo scp -r -P 10022 root@192.168.21.4:/www/wwwroot/xxxx/xxxx/pre/backend-pre/mysql_bk_202407251630.sql ./

可能需要先删除测试环境的外键 
[ltkj@m mysql]$ mysql -u root -p ltkj-personnel-manager-pre < ./byteayyra-1030.sql