MySQL在Linux中的安装和使用

下面介绍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、开始安装软件,安装软件顺序:

  1. 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]# 
  1. 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]# 
  1. 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]# 
  1. 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启动数据库并配置主从

  1. docker下载mysql镜像:
docker pull mysql:8.0.32
  1. 创建容器固定IP:
docker network create --subnet=192.168.56.0/24 mysql-network
  1. 宿主机创建配置文件目录:
mkdir -p /mysql/master/conf.d
mkdir -p /mysql/slave1/conf.d
mkdir -p /mysql/slave2/conf.d
  1. 初始化容器:

(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. 配置参数:

(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';
  1. 创建用户:
mysql> create user repl@'%' identified with mysql_native_password by 'qwe123';
mysql> grant REPLICATION CLIENT,REPLICATION SLAVE on *.* to repl@'%';
mysql> 
  1. 登录到从库执行下面命令:
mysql> change master to master_host='192.168.56.116',
master_port=3306,
master_user='repl',
master_password='qwe123',
master_auto_position=1;
  1. 从库启动
mysql> start slave;
  1. 查看从库状态:
mysql> show slave status \G

4、其他主从相关命令:

  1. 停掉从库的同步
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> 
  1. 主库表处于只读状态,不允许写
flush  tables with read lock;
  1. 恢复表写入
unlock tables;
  1. 清除主从同步信息
reset slave all;
  1. 重置主节点:
reset master;
  1. 查看主节点
show master status \G
  1. 在[mysqld]标签内增加如下内容设置binlog过期时间:
expire_logs_days=30
max_binlog_size=1024M
  1. 查看binlog过期时间
show variables like 'expire_logs_days';
  1. 将binlog.000002之前的日志都删除:
purge binary logs to 'binlog.000002';
  1. 设置从库为只读模式:
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> 
  1. 用户授权:
grant INSERT,DELETE,UPDATE,SELECT,DROP,INDEX,CREATE on *.* to 'xingo'@'%' with grant option;
  1. 回收权限:
revoke ALL on *.*  from 'xingo'@'%';
  1. 查看用户权限:
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> 
  1. 删除用户:
DROP USER 'account_name';
DROP USER user@host
  1. 设置主从同步位置:
set session gtid_next='61f5626f-b1bd-11ed-bc0c-080027dcfa26:1';
set session gtid_next = AUTOMATIC;
  1. 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]# 
  1. 导出数据:
mysqldump --all-databases > db.dump

https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-index-syntax-notes

https://www.php.cn/redis/421748.html

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
### 回答1: 在Linux安装MySQL,可以按照以下步骤操作: 1. 打开终端,更新apt-get:`sudo apt-get update` 2. 安装MySQL Server:`sudo apt-get install mysql-server` 3. 安装完成后,MySQL Server将自动启动。可以使用以下命令检查MySQL Server是否正在运行:`systemctl status mysql.service` 4. 如果MySQL Server未运行,可以使用以下命令手动启动:`sudo systemctl start mysql` 5. 如果您想让MySQL Server在系统启动时自动启动,可以使用以下命令:`sudo systemctl enable mysql` 以上步骤适用于Ubuntu和Debian系统,如果您使用的是其他Linux发行版,可能需要使用不同的包管理器或命令。 ### 回答2: 在Linux安装MySQL非常简单,可以按照以下步骤进行操作: 1. 打开终端,使用root权限或者sudo命令来执行以下步骤。 2. 更新系统软件仓库,使用命令`sudo apt update`。 3. 安装MySQL服务器软件,使用命令`sudo apt install mysql-server`。 4. 安装过程,会提示输入MySQL的root用户密码。密码设置完成后,继续安装过程。 5. 安装完成后,会自动启动MySQL服务。可以使用命令`sudo systemctl status mysql`来检查MySQL服务状态。 6. 如果MySQL服务未启动,可以使用命令`sudo systemctl start mysql`来手动启动服务。 7. 还可以使用`sudo systemctl enable mysql`命令来设置MySQL服务开机自启动。 8. 安装完成后,默认的MySQL服务器配置是较为简单的,可以通过编辑`sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf`文件来进行更高级的配置。 9. 如果需要访问MySQL服务器,可以使用`mysql -u root -p`命令登录。输入之前设置的root密码后,可以进行MySQL的相关操作。 这样,您就成功在Linux安装MySQL数据库。在安装完成后,您可以根据需要进一步配置和管理MySQL服务器。 ### 回答3: 在Linux安装MySQL非常简单。以下是安装MySQL的步骤: 1. 打开终端窗口,并使用root用户登录。 2. 运行以下命令更新系统软件包列表: ```bash sudo apt update ``` 3. 运行以下命令来安装MySQL服务器: ```bash sudo apt install mysql-server ``` 4. 安装过程,将会提示设置MySQL root用户的密码。输入密码并确认。 5. 安装完成后,可以运行以下命令来检查MySQL服务的运行状态: ```bash sudo service mysql status ``` 如果MySQL正常运行,将显示"active (running)"的信息。 6. 若要登录到MySQL服务器,可以运行以下命令: ```bash sudo mysql -u root -p ``` 输入先前设置的密码即可登录到MySQL。 通过以上步骤,您已成功在Linux安装MySQL

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值