MySQL在Linux中的安装和使用

腊笔不小新xingo

已于 2023-12-19 19:52:44 修改

阅读量208

点赞数

文章标签： 1024程序员节 mysql linux

于 2023-10-24 16:33:02 首次发布

本文链接：https://blog.csdn.net/dream_xin2013/article/details/133632169

版权

下面介绍MySQL数据库在Linux中的安装和使用：

一、rpm安装

测试安装MySQL是在centos7中，由于我的centos是最小化安装，很多相关的软件都没有安装，所以这里需要先安装相关包：

1、习惯用vim进行文本编辑，先安装vim包：

yum install vim

2、centos7中默认没有ifconfig命令用于查看网络信息，已经习惯使用这个命令需要安装net-tools包：

yum install net-tools

3、发行版本默认都是带了mariadb，需要卸载mariadb：

rpm -qa | grep mariadb
rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64

4、其他安装常用工具和包如下：

yum install lrzsz wget perl-Digest-MD5
yum install numactl
yum install ncurses ncurses-devel openssl-devel bison gcc gcc-c++ make

5、添加用户和组，并修改密码：

[root@localhost ~]# groupadd mysql
[root@localhost ~]# useradd -g mysql -d /home/mysql mysql
[root@localhost ~]# passwd mysql
Changing password for user mysql.
New password: 
BAD PASSWORD: The password is shorter than 8 characters
Retype new password: 
passwd: all authentication tokens updated successfully.
[root@localhost ~]#

6、从官网下载的安装包，并校验下载的文件是否完整，官网下载的包都提供了校验的md5值，下载不同的版本对应的md5值可能不同，我下载文件的 mysql md5 : 7743c9f29acb3344729e0fbc6ba72013

[root@localhost ~]# md5sum mysql-8.0.32-1.el7.x86_64.rpm-bundle.tar 
7743c9f29acb3344729e0fbc6ba72013  mysql-8.0.32-1.el7.x86_64.rpm-bundle.tar
[root@localhost ~]#

7、解压安装包：

[root@localhost ~]# tar -xvf mysql-8.0.32-1.el7.x86_64.rpm-bundle.tar -C /home/mysql/
mysql-community-client-8.0.32-1.el7.x86_64.rpm
mysql-community-client-plugins-8.0.32-1.el7.x86_64.rpm
mysql-community-common-8.0.32-1.el7.x86_64.rpm
mysql-community-debuginfo-8.0.32-1.el7.x86_64.rpm
mysql-community-devel-8.0.32-1.el7.x86_64.rpm
mysql-community-embedded-compat-8.0.32-1.el7.x86_64.rpm
mysql-community-icu-data-files-8.0.32-1.el7.x86_64.rpm
mysql-community-libs-8.0.32-1.el7.x86_64.rpm
mysql-community-libs-compat-8.0.32-1.el7.x86_64.rpm
mysql-community-server-8.0.32-1.el7.x86_64.rpm
mysql-community-server-debug-8.0.32-1.el7.x86_64.rpm
mysql-community-test-8.0.32-1.el7.x86_64.rpm
[root@localhost ~]#

真正需要安装的包只有4个：

mysql-community-common-8.0.32-1.el9.x86_64.rpm
mysql-community-libs-8.0.32-1.el9.x86_64.rpm
mysql-community-client-8.0.32-1.el9.x86_64.rpm
mysql-community-server-8.0.32-1.el9.x86_64.rpm

调整设置目录权限：

[root@localhost ~]# chown -R mysql:mysql /home/mysql

8、开始安装软件，安装软件顺序：

common包安装：

[root@localhost mysql]# rpm -ivh mysql-community-common-8.0.32-1.el7.x86_64.rpm 
warning: mysql-community-common-8.0.32-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-common-8.0.32-1.e################################# [100%]
[root@localhost mysql]#

libs安装：

[root@localhost mysql]# rpm -ivh --force --nodeps mysql-community-libs-8.0.32-1.el7.x86_64.rpm 
warning: mysql-community-libs-8.0.32-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-libs-8.0.32-1.el7################################# [100%]
[root@localhost mysql]#

client安装：

[root@localhost mysql]# rpm -ivh --force --nodeps mysql-community-client-8.0.32-1.el7.x86_64.rpm 
warning: mysql-community-client-8.0.32-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-client-8.0.32-1.e################################# [100%]
[root@localhost mysql]#

server安装：

[root@localhost mysql]# rpm -ivh --force --nodeps mysql-community-server-8.0.32-1.el7.x86_64.rpm 
warning: mysql-community-server-8.0.32-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-server-8.0.32-1.e################################# [100%]
[root@localhost mysql]#

9、初始化：
在初始化前，需要先添加一个配置在/etc/my.cnf文件中，表示数据库对表名大小写不敏感：

lower_case_table_names=1

因为在Mysql8之后linux下默认对表名大小写敏感，并且表明设置大小写不敏感必须在初始化前进行，如果初始化后在调整这个参数值，服务启动时就会报错：

Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

查看日志内容有如下信息：

2023-12-19T10:21:50.286344Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').
2023-12-19T10:21:50.286584Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2023-12-19T10:21:50.286875Z 0 [ERROR] [MY-010119] [Server] Aborting

执行数据库初始化命令：

[root@localhost mysql]# mysqld --initialize
[root@localhost mysql]#

10、修改目录权限：

[root@localhost mysql]# ls -lh /var/lib/mysql
total 77M
-rw-r-----. 1 root root   56 Feb 20 09:01 auto.cnf
-rw-------. 1 root root 1.7K Feb 20 09:01 ca-key.pem
-rw-r--r--. 1 root root 1.1K Feb 20 09:01 ca.pem
-rw-r--r--. 1 root root 1.1K Feb 20 09:01 client-cert.pem
-rw-------. 1 root root 1.7K Feb 20 09:01 client-key.pem
-rw-r-----. 1 root root 192K Feb 20 09:01 #ib_16384_0.dblwr
-rw-r-----. 1 root root 8.2M Feb 20 09:01 #ib_16384_1.dblwr
-rw-r-----. 1 root root 5.5K Feb 20 09:01 ib_buffer_pool
-rw-r-----. 1 root root  12M Feb 20 09:01 ibdata1
drwxr-x---. 2 root root 4.0K Feb 20 09:01 #innodb_redo
drwxr-x---. 2 root root    6 Feb 20 09:01 #innodb_temp
drwxr-x---. 2 root root  143 Feb 20 09:01 mysql
-rw-r-----. 1 root root  24M Feb 20 09:01 mysql.ibd
drwxr-x---. 2 root root 8.0K Feb 20 09:01 performance_schema
-rw-------. 1 root root 1.7K Feb 20 09:01 private_key.pem
-rw-r--r--. 1 root root  452 Feb 20 09:01 public_key.pem
-rw-r--r--. 1 root root 1.1K Feb 20 09:01 server-cert.pem
-rw-------. 1 root root 1.7K Feb 20 09:01 server-key.pem
drwxr-x---. 2 root root   28 Feb 20 09:01 sys
-rw-r-----. 1 root root  16M Feb 20 09:01 undo_001
-rw-r-----. 1 root root  16M Feb 20 09:01 undo_002
[root@localhost mysql]# 
[root@localhost mysql]# chown mysql:mysql -R /var/lib/mysql
[root@localhost mysql]# ls -lh /var/lib/mysql
total 77M
-rw-r-----. 1 mysql mysql   56 Feb 20 09:01 auto.cnf
-rw-------. 1 mysql mysql 1.7K Feb 20 09:01 ca-key.pem
-rw-r--r--. 1 mysql mysql 1.1K Feb 20 09:01 ca.pem
-rw-r--r--. 1 mysql mysql 1.1K Feb 20 09:01 client-cert.pem
-rw-------. 1 mysql mysql 1.7K Feb 20 09:01 client-key.pem
-rw-r-----. 1 mysql mysql 192K Feb 20 09:01 #ib_16384_0.dblwr
-rw-r-----. 1 mysql mysql 8.2M Feb 20 09:01 #ib_16384_1.dblwr
-rw-r-----. 1 mysql mysql 5.5K Feb 20 09:01 ib_buffer_pool
-rw-r-----. 1 mysql mysql  12M Feb 20 09:01 ibdata1
drwxr-x---. 2 mysql mysql 4.0K Feb 20 09:01 #innodb_redo
drwxr-x---. 2 mysql mysql    6 Feb 20 09:01 #innodb_temp
drwxr-x---. 2 mysql mysql  143 Feb 20 09:01 mysql
-rw-r-----. 1 mysql mysql  24M Feb 20 09:01 mysql.ibd
drwxr-x---. 2 mysql mysql 8.0K Feb 20 09:01 performance_schema
-rw-------. 1 mysql mysql 1.7K Feb 20 09:01 private_key.pem
-rw-r--r--. 1 mysql mysql  452 Feb 20 09:01 public_key.pem
-rw-r--r--. 1 mysql mysql 1.1K Feb 20 09:01 server-cert.pem
-rw-------. 1 mysql mysql 1.7K Feb 20 09:01 server-key.pem
drwxr-x---. 2 mysql mysql   28 Feb 20 09:01 sys
-rw-r-----. 1 mysql mysql  16M Feb 20 09:01 undo_001
-rw-r-----. 1 mysql mysql  16M Feb 20 09:01 undo_002
[root@localhost mysql]#

11、根据自己的环境修改配置文件，配置文件位置：/etc/my.cnf
如果使用默认配置，这一步可以省略，我的配置文件稍做了如下调整：
修改配置文件：

[root@localhost mysql]# vim /etc/my.cnf

配置数据目录：
datadir=/home/mysql/data
socket=/home/mysql/mysql.sock

配置日志文件：
log-error=/home/mysql/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

12、启动服务并查看服务状态：

[root@localhost mysql]# systemctl start mysqld
[root@localhost mysql]# 
[root@localhost mysql]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2023-02-20 09:12:33 CST; 8s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 25407 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 25430 (mysqld)
   Status: "Server is operational"
   CGroup: /system.slice/mysqld.service
           └─25430 /usr/sbin/mysqld

Feb 20 09:12:32 localhost.localdomain systemd[1]: Starting MySQL Server...
Feb 20 09:12:33 localhost.localdomain systemd[1]: Started MySQL Server.
[root@localhost mysql]#

13、设置服务自启动：

[root@localhost mysql]# systemctl enable mysqld
[root@localhost mysql]#

14、服务启动后会自动创建一个随机密码，随机密码用于初次登录系统使用，进入系统后会提示修改密码，否则其他功能都是不允许操作的。
查看初始化后的默认密码：

[root@localhost mysql]# cat /var/log/mysqld.log | grep password
2023-02-20T01:01:23.906749Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: V&ZPwN1*q+w<
[root@localhost mysql]#

首次进入后需要重置密码：

[root@localhost mysql]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.32

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> 
mysql> ALTER USER USER() IDENTIFIED BY 'qwe123';
mysql>

其他相关命令：

# 查看用户和可登录的服务器地址：
mysql> select host,user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)

mysql> 

# 新建一个用户：
mysql> create user xingo@'%' identified with caching_sha2_password by 'xingoTest';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all on *.* to xingo@'%' with grant option;
Query OK, 0 rows affected (0.07 sec)

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

mysql> 

# 查看当前连接信息：
mysql> status
--------------
mysql  Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:		10
Current database:	mysql
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		8.0.32 MySQL Community Server - GPL
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/var/lib/mysql/mysql.sock
Binary data as:		Hexadecimal
Uptime:			20 min 31 sec

Threads: 2  Questions: 61  Slow queries: 0  Opens: 194  Flush tables: 3  Open tables: 110  Queries per second avg: 0.049
--------------

mysql> 

# 查找mysql配置文件位置：
[root@localhost ~]# mysql --help|grep my.cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

二、二进制安装：

1、安装相关依赖：

[root@localhost ~]# yum install ncurses ncurses-devel openssl-devel bison gcc gcc-c++ make

2、校验文件：

8734032504fce80d843ca1685518ed00
[root@localhost ~]# md5sum mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz 
8734032504fce80d843ca1685518ed00  mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz
[root@localhost ~]#

3、创建相关目录：

mkdir -p /mysql/data/
mkdir -p /mysql/app/
mkdir -p /mysql/conf/

4、创建用户和组：

[root@localhost ~]# groupadd mysql
[root@localhost ~]# useradd -g mysql mysql
[root@localhost ~]# chown -R mysql:mysql /mysql
[root@localhost ~]# 
[root@localhost ~]# passwd mysql
Changing password for user mysql.
New password: 
BAD PASSWORD: The password is shorter than 8 characters
Retype new password: 
passwd: all authentication tokens updated successfully.
[root@localhost ~]#

5、上传二进制包：

[root@localhost ~]# mv mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz /mysql/app/
[root@localhost ~]# cd /mysql/app/
[root@localhost app]# 
[root@localhost app]# chown -R mysql:mysql /mysql/
[root@localhost app]# ls -lh
total 583M
-rw-r--r--. 1 mysql mysql 583M Feb 20 09:49 mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz
[root@localhost app]#

6、解压二进制包并重命名文件目录：

[root@localhost app]# tar -xvf mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz 
[root@localhost app]# mv mysql-8.0.32-linux-glibc2.12-x86_64 mysql-8.0.32
[root@localhost app]#

7、切换到mysql用户配置环境变量：

[mysql@localhost ~]$ vim .bash_profile

MYSQL_HOME=/mysql/app/mysql-8.0.32
PATH=$PATH:$HOME/.local/bin:$HOME/bin:$MYSQL_HOME/bin

8、配置文件：

[mysql@localhost mysql]$ vim /mysql/conf/my.cnf

[client]
default-character-set = utf8mb4
socket = /mysql/data/mysql.sock

[mysqld]
port=3306
character_set_server=utf8mb4
secure_file_priv=
server-id=2
binlog_format=row
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
skip-name-resolve
gtid-mode = ON
enforce-gtid-consistency = on
report_host = 192.168.56.117

default-storage-engine = InnoDB
basedir = /mysql/app/mysql-8.0.32
datadir = /mysql/data/
pid-file = /mysql/data/mysqld.pid
max_connections = 2000
back_log = 600
wait_timeout = 1800
max_user_connections = 1000
innodb_buffer_pool_size = 1024M
innodb_log_file_size = 512M
innodb_log_buffer_size = 40M

# 慢查询日志
slow_query_log = ON
long_query_time = 5
slow_query_log_file = /mysql/data/slow.log

# 错误日志
log-error = /mysql/data/mysqld.log
log_error_verbosity = 3

# binlog配置
log_bin = /mysql/data/binlog
log_bin_index = /mysql/data/binlog.index

9、查看环境变量：

[mysql@localhost mysql]$ which mysqld
/mysql/app/mysql-8.0.32/bin/mysqld
[mysql@localhost mysql]$

10、数据库初始化：

[mysql@localhost mysql]$ mysqld --defaults-file=/mysql/conf/my.cnf --initialize --user=mysql --basedir=/mysql/app/mysql-8.0.32 --datadir=/mysql/data/
[mysql@localhost mysql]$

11、启动数据库：

[mysql@localhost mysql]$ mysqld_safe --defaults-file=/mysql/conf/my.cnf --user=mysql &
[1] 1533
[mysql@localhost mysql]$ 2023-02-20T03:31:33.316119Z mysqld_safe Logging to '/mysql/data/mysql3306/errlog/mysqld.log'.
2023-02-20T03:31:33.352494Z mysqld_safe Starting mysqld daemon with databases from /mysql/data/mysql3306/data

12、关闭数据库：

[mysql@localhost bin]$ mysqladmin -uroot -p shutdown
Enter password: 
[mysql@localhost bin]$

13、查看mysql服务状态：

[mysql@localhost mysql]$ ps -ef | grep mysql
root      1354  1337  0 11:25 pts/0    00:00:00 su - mysql
mysql     1355  1354  0 11:25 pts/0    00:00:00 -bash
mysql     1533  1355  0 11:31 pts/0    00:00:00 /bin/sh /mysql/app/mysql-8.0.32/bin/mysqld_safe --defaults-file=/mysql/conf/my.cnf --user=mysql
mysql     1836  1533  8 11:31 pts/0    00:00:02 /mysql/app/mysql-8.0.32/bin/mysqld --defaults-file=/mysql/conf/my.cnf --basedir=/mysql/app/mysql-8.0.32 --datadir=/mysql/data/mysql3306/data --plugin-dir=/mysql/app/mysql-8.0.32/lib/plugin --log-error=/mysql/data/mysql3306/errlog/mysqld.log --pid-file=/mysql/data/mysql3306/pid/mysqld.pid
mysql     1892  1355  0 11:31 pts/0    00:00:00 ps -ef
mysql     1893  1355  0 11:31 pts/0    00:00:00 grep --color=auto mysql
[mysql@localhost mysql]$ 
[mysql@localhost mysql]$ top
top - 11:32:03 up 7 min,  1 user,  load average: 0.26, 0.13, 0.06
Tasks: 100 total,   1 running,  99 sleeping,   0 stopped,   0 zombie
%Cpu(s):  0.0 us,  3.2 sy,  0.0 ni, 96.8 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem :  1881928 total,    70308 free,   556444 used,  1255176 buff/cache
KiB Swap:  4198396 total,  4198396 free,        0 used.  1163316 avail Mem 

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                                                      
    1 root      20   0  125392   3836   2580 S   0.0  0.2   0:00.80 systemd                                                                                      
    2 root      20   0       0      0      0 S   0.0  0.0   0:00.00 kthreadd                                                                                     
    4 root       0 -20       0      0      0 S   0.0  0.0   0:00.00 kworker/0:0H                                                                                 
    5 root      20   0       0      0      0 S   0.0  0.0   0:00.00 kworker/u4:0

14、查看数据库临时密码：

[mysql@localhost mysql]$ cat /mysql/data/mysqld.log | grep password
2023-02-20T03:31:10.055162Z 0 [Note] [MY-010309] [Server] Auto generated RSA key files through --sha256_password_auto_generate_rsa_keys are placed in data directory.
2023-02-20T03:31:10.055199Z 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --caching_sha2_password_auto_generate_rsa_keys as key files are present in data directory.
2023-02-20T03:31:10.056850Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: >*#kdRpPh0cx
2023-02-20T03:31:37.380998Z 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --sha256_password_auto_generate_rsa_keys as key files are present in data directory.
2023-02-20T03:31:37.381039Z 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --caching_sha2_password_auto_generate_rsa_keys as key files are present in data directory.
[mysql@localhost mysql]$

====================================

三、使用docker启动数据库并配置主从

docker下载mysql镜像：

docker pull mysql:8.0.32

创建容器固定IP：

docker network create --subnet=192.168.56.0/24 mysql-network

mkdir -p /mysql/master/conf.d
mkdir -p /mysql/slave1/conf.d
mkdir -p /mysql/slave2/conf.d

初始化容器：

（1）主库：

docker run -d --name mysql-masterpod \    # 容器名称
-h master -p 33062:3306 --net=mysql-network --ip 192.168.56.8 \      # 端口映射和ip地址
-v /mysql/master/conf.d:/etc/mysql/conf.d \          # 配置文件映射
-e MYSQL_ROOT_PASSWORD=456123 \         # 数据库root用户密码
mysql:8.0.32     # 镜像名

主库设置开机自启动

docker update --restart=always mysql-masterpod

（2）从库1：

docker run -d --name mysql-slavepod1 \
-h slave1 -p 33063:3306 --net=mysql-network --ip 192.168.56.9 \
-v /mysql/slave1/conf.d:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=456123 \
mysql:8.0.32

从库1设置开机自启动

docker update --restart=always mysql-slavepod1

（3）从库2：

docker run -d --name mysql-slavepod2 \
-h slave1 -p 33064:3306 --net=mysql-network --ip 192.168.56.10 \
-v /mysql/slave2/conf.d:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=456123 \
mysql:8.0.32

从库2设置开机自启动

docker update --restart=always mysql-slavepod2

配置参数：

（1）主库参数：

[mysqld]
port=3306
character_set_server=utf8mb4
secure_file_priv=
server-id=1
log-bin=ON
binlog_format=row
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
skip-name-resolve
gtid-mode = ON
enforce-gtid-consistency = on
report_host = 192.168.56.116

（1）从库1参数：

[mysqld]
port=3306
character_set_server=utf8mb4
secure_file_priv=
server-id=2
log-bin=ON
binlog_format=row
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
skip-name-resolve
gtid-mode = ON
enforce-gtid-consistency = on
report_host = 192.168.56.117

（3）从库2参数：

[mysqld]
port=3306
character_set_server=utf8mb4
secure_file_priv=
server-id=3
log-bin=ON
binlog_format=row
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
skip-name-resolve
gtid-mode = ON
enforce-gtid-consistency = on
report_host = 192.168.56.118

其他有关mysql常见处理：

1、mysql 忘记root密码处理：

# 在配置文件加一个命令 ： 
skip-grant-tables

# 重启服务 ： 
systemctl restart mysqld

# 先刷新权限在修改密码：
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> alter user root@'localhost' identified by '456123';
Query OK, 0 rows affected (0.03 sec)

mysql> 

# 退出后删除配置信息 skip-grant-tables 重启服务器：
[root@localhost mysql]# vim /etc/my.cnf
[root@localhost mysql]# systemctl restart mysqld
[root@localhost mysql]# 
[root@localhost mysql]# mysql -uroot -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@localhost mysql]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> exit
Bye
[root@localhost mysql]#

3、主从搭建：
查看主库binlog是否开启：

mysql> show variables like 'log_bin';

创建用户：

mysql> create user repl@'%' identified with mysql_native_password by 'qwe123';
mysql> grant REPLICATION CLIENT,REPLICATION SLAVE on *.* to repl@'%';
mysql>

登录到从库执行下面命令：

mysql> change master to master_host='192.168.56.116',
master_port=3306,
master_user='repl',
master_password='qwe123',
master_auto_position=1;

从库启动

mysql> start slave;

查看从库状态：

mysql> show slave status \G

4、其他主从相关命令：

停掉从库的同步

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql>

主库表处于只读状态，不允许写

flush  tables with read lock;

恢复表写入

unlock tables;

清除主从同步信息

reset slave all;

重置主节点：

reset master;

查看主节点

show master status \G

在[mysqld]标签内增加如下内容设置binlog过期时间：

expire_logs_days=30
max_binlog_size=1024M

查看binlog过期时间

show variables like 'expire_logs_days';

将binlog.000002之前的日志都删除：

purge binary logs to 'binlog.000002';

设置从库为只读模式：

mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like "%read_only%";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
+-----------------------+-------+
4 rows in set (0.03 sec)

mysql>

用户授权：

grant INSERT,DELETE,UPDATE,SELECT,DROP,INDEX,CREATE on *.* to 'xingo'@'%' with grant option;

回收权限：

revoke ALL on *.*  from 'xingo'@'%';

查看用户权限：

SHOW GRANTS FOR 'xingo'@'%' \G

mysql> use kdtest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> delete from t_customer where fid=3;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
mysql>

删除用户：

DROP USER 'account_name';
DROP USER user@host

设置主从同步位置：

set session gtid_next='61f5626f-b1bd-11ed-bc0c-080027dcfa26:1';
set session gtid_next = AUTOMATIC;

rpm卸载mysql：

[root@localhost mysql]# rpm -qa|grep -i mysql
mysql-community-common-8.0.32-1.el7.x86_64
mysql-community-client-8.0.32-1.el7.x86_64
mysql-community-libs-8.0.32-1.el7.x86_64
mysql-community-server-8.0.32-1.el7.x86_64
[root@localhost mysql]# 
[root@localhost mysql]# 
[root@localhost mysql]# rpm -e --nodeps mysql-community-common-8.0.32-1.el7.x86_64
[root@localhost mysql]# rpm -e --nodeps mysql-community-client-8.0.32-1.el7.x86_64
[root@localhost mysql]# rpm -e --nodeps mysql-community-libs-8.0.32-1.el7.x86_64
[root@localhost mysql]# rpm -e --nodeps mysql-community-server-8.0.32-1.el7.x86_64

# 查找相关目录删除：
[root@localhost mysql]# find / -name mysql
/etc/selinux/targeted/active/modules/100/mysql
/var/lib/mysql
/var/lib/mysql/mysql
/var/spool/mail/mysql
/usr/lib64/mysql
/home/mysql

# 删除mysql配置文件：
[root@localhost mysql]# rm -rf /etc/my.cnf
[root@localhost mysql]# 

# 查看是否还有mysql相关信息：
[root@localhost mysql]# rpm -qa|grep -i mysql
[root@localhost mysql]#