MySQL进阶篇
1. 二进制格式mysql安装
下载二进制格式的mysql软件包，然后上传，如图所示
去mysql官网下载最新版本
用xftp上传
[root@localhost ~]# ls
anaconda-ks.cfg mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
[root@localhost ~]#
//创建用户和组
[root@localhost ~]# id mysql
id: “mysql”：无此用户
[root@localhost ~]# useradd -r -m -s /sbin/nologin mysql
[root@localhost ~]# id mysql
uid=994(mysql) gid=991(mysql) 组=991(mysql)
[root@localhost ~]# grep mysql /etc/group
mysql:x:991:
//解压软件至/usr/local/
[root@localhost ~]# ls /usr/local
bin etc games include lib lib64 libexec sbin share src
[root@localhost ~]# tar xf mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost ~]# ls /usr/local
bin games lib libexec sbin src
etc include lib64 mysql-5.7.33-linux-glibc2.12-x86_64 share
//因为名字太长可以给他重命名或者做一个软链接
[root@localhost local]# ln -sv mysql-5.7.33-linux-glibc2.12-x86_64/ mysql
'mysql' -> 'mysql-5.7.33-linux-glibc2.12-x86_64/'
[root@localhost local]# ll /usr/local/
总用量 0
drwxr-xr-x. 2 root root 6 5月 19 2020 bin
drwxr-xr-x. 2 root root 6 5月 19 2020 etc
drwxr-xr-x. 2 root root 6 5月 19 2020 games
drwxr-xr-x. 2 root root 6 5月 19 2020 include
drwxr-xr-x. 2 root root 6 5月 19 2020 lib
drwxr-xr-x. 3 root root 17 4月 28 23:10 lib64
drwxr-xr-x. 2 root root 6 5月 19 2020 libexec
lrwxrwxrwx. 1 root root 36 5月 5 11:10 mysql -> mysql-5.7.33-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 5月 5 11:03 mysql-5.7.33-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 5月 19 2020 sbin
drwxr-xr-x. 5 root root 49 4月 28 23:10 share
drwxr-xr-x. 2 root root 6 5月 19 2020 src
//修改目录/usr/local/mysql的属主属组
[root@localhost local]# chown -R mysql.mysql /usr/local/mysql*
[root@localhost local]# ll /usr/local/
总用量 0
drwxr-xr-x. 2 root root 6 5月 19 2020 bin
drwxr-xr-x. 2 root root 6 5月 19 2020 etc
drwxr-xr-x. 2 root root 6 5月 19 2020 games
drwxr-xr-x. 2 root root 6 5月 19 2020 include
drwxr-xr-x. 2 root root 6 5月 19 2020 lib
drwxr-xr-x. 3 root root 17 4月 28 23:10 lib64
drwxr-xr-x. 2 root root 6 5月 19 2020 libexec
lrwxrwxrwx. 1 mysql mysql 36 5月 5 11:10 mysql -> mysql-5.7.33-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 mysql mysql 129 5月 5 11:03 mysql-5.7.33-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 5月 19 2020 sbin
drwxr-xr-x. 5 root root 49 4月 28 23:10 share
drwxr-xr-x. 2 root root 6 5月 19 2020 src
//设置环境变量
[root@localhost local]# ls /usr/local/mysql
bin docs include lib LICENSE man README share support-files
[root@localhost local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@localhost local]# source /etc/profile.d/mysql.sh
[root@localhost local]# which mysql
/usr/local/mysql/bin/mysql
//建立数据存放目录
[root@localhost local]# mkdir /opt/data
[root@localhost local]# ll /opt/
总用量 0
drwxr-xr-x. 2 root root 6 5月 5 11:28 data
[root@localhost local]# chown -R mysql.mysql /opt/data/
[root@localhost local]# ll /opt/
总用量 0
drwxr-xr-x. 2 mysql mysql 6 5月 5 11:28 data
//初始化数据库
[root@localhost local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
2021-05-05T03:32:05.943403Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-05T03:32:06.440368Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-05-05T03:32:06.513123Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-05-05T03:32:06.575674Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 775294b0-ad52-11eb-abb9-000c2948795d.
2021-05-05T03:32:06.578494Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-05-05T03:32:07.466379Z 0 [Warning] CA certificate ca.pem is self signed.
2021-05-05T03:32:07.555027Z 1 [Note] A temporary password is generated for root@localhost: Reyn9NflCr*%
[root@localhost local]# ls /opt/data/
auto.cnf ca.pem client-key.pem ibdata1 ib_logfile1 performance_schema public_key.pem server-key.pem
ca-key.pem client-cert.pem ib_buffer_pool ib_logfile0 mysql private_key.pem server-cert.pem sys
[root@localhost local]# echo 'Reyn9NflCr*%'
Reyn9NflCr*%
[root@localhost local]# echo 'Reyn9NflCr*%' > pass
//此处Reyn9NflCr*%是一个临时密码，我们将它写入一个文件，因为后面登录要用到，怕忘记，所以记录一下。
//生成配置文件
[root@localhost local]# ls /etc/my.cnf //8.0版本之后没有这个文件，7.0之前有，如果以后去到企业，有这个文件，一定记得备份之后再修改
ls: 无法访问'/etc/my.cnf': 没有那个文件或目录
[root@localhost local]# vim /etc/my.cnf
[mysqld] //服务端mysql进程
basedir = /usr/local/mysql //安装目录
datadir = /opt/data //数据存放的位置
socket = /tmp/mysql.sock //套接字
port = 3306 //端口号,默认是3306
pid-file = /opt/data/mysql.pid //进程文件
user = mysql //我的用户
skip-name-resolve //调过域名解析，直接用IP连
[root@localhost local]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
//配置服务启动脚本
[root@localhost local]# cd /usr/local/mysql
[root@localhost mysql]# ls
bin docs include lib LICENSE man README share support-files
[root@localhost mysql]# cd support-files/
[root@localhost support-files]# ls
magic mysqld_multi.server mysql-log-rotate mysql.server
[root@localhost support-files]# file mysql.server
mysql.server: POSIX shell script, ASCII text executable
[root@localhost support-files]# cp mysql.server /etc/init.d/mysqld
[root@localhost support-files]# cd
[root@localhost ~]# ll /etc/init.d/mysqld
-rwxr-xr-x. 1 root root 10576 5月 5 12:23 /etc/init.d/mysqld
[root@localhost ~]# vim /etc/init.d/mysqld
//找到如下两行
basedir=/usr/local/mysql //在=后面添加它的路径
datadir=/opt/data //在=后面添加它的路径
//启动mysql
[root@localhost ~]# service mysqld start
Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'.
. SUCCESS!
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 80 *:3306 *:*
//修改登录密码
//先用临时密码 Reyn9NflCr*% 登录
[root@localhost ~]# mysql -uroot -p'Reyn9NflCr*%'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.33
Copyright (c) 2000, 2021, 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> set password = password('lixirong123!'); //修改密码
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> quit
Bye
[root@localhost ~]# mysql -uroot -p'lixirong123!' //用新密码登录
总结一下二进制安装mysql的步骤：
1、去官网下载安装包
2、创建用户
3、解压至指定目录
4、创建软链接
5、修改属主属组
6、设置环境变量
7、创建数据库存放目录并修改属主属组
8、初始化并保存密码
9、修改配置文件
10、配置启动脚本
11、启动并设置密码
2. mysql配置文件
mysql的配置文件为/etc/my.cnf
配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效
/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf
mysql常用配置文件参数：
| 参数 | 说明 |
|---|---|
| port = 3306 | 设置监听端口 |
| socket = /tmp/mysql.sock | 指定套接字文件位置 |
| basedir = /usr/local/mysql | 指定MySQL的安装路径 |
| datadir = /data/mysql | 指定MySQL的数据存放路径 |
| pid-file = /data/mysql/mysql.pid | 指定进程ID文件存放路径 |
| user = mysql | 指定MySQL以什么用户的身份提供服务 |
| skip-name-resolve | 禁止MySQL对外部连接进行DNS解析 使用这一选项可以消除MySQL进行DNS解析的时间。 若开启该选项，则所有远程主机连接授权都要使用IP地址方 式否则MySQL将无法正常处理连接请求 |
3. mysql数据库备份与恢复
3.1 数据库常用备份方案
数据库备份方案：
- 全量备份
- 增量备份
- 差异备份
| 备份方案 | 特点 |
|---|---|
| 全量备份 | 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。 数据恢复快。 备份时间长 |
| 增量备份 | 增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份 与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象 是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量 备份后所产生的增加和修改的文件，如此类推。 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行 |
| 差异备份 | 备份上一次的完全备份后发生变化的所有文件。 差异备份是指在一次全备份后到进行差异备份的这段时间内 对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。 |
3.2 mysql备份工具mysqldump
//语法：
mysqldump [OPTIONS] database [tables ...]
mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
//常用的OPTIONS：
-uUSERNAME //指定数据库用户名
-hHOST //指定服务器主机，请使用ip地址
-pPASSWORD //指定数据库用户的密码
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
//备份整个数据库(全备)
[root@localhost ~]# mysqldump -uroot -plixirong123! --all-databases > all-$(date "+%Y%m%d").sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-20210505.sql anaconda-ks.cfg mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
//备份school库的student表和class1表
[root@localhost ~]# mysqldump -uroot -plixirong123! school class1 student > all-table.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-20210505.sql all-table.sql anaconda-ks.cfg mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
//备份school库
[root@localhost ~]# mysqldump -uroot -plixirong123! --databases school > school.sql;
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-20210505.sql all-table.sql anaconda-ks.cfg mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz school.sql
//模拟误删school数据库
mysql> drop database lixirong;
ERROR 1008 (HY000): Can't drop database 'lixirong'; database doesn't exist
mysql> drop database school;
Query OK, 2 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
3.3 mysql数据恢复
//恢复school数据库
[root@localhost ~]# ls
all-20210505.sql all-table.sql anaconda-ks.cfg mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz school.sql
[root@localhost ~]# mysql -uroot -plixirong123! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
3.4 差异备份与恢复
3.4.1. mysql差异备份
开启MySQL服务器的二进制日志功能
[root@localhost ~]# vi /etc/my.cnf
[mysqld]
server-id = 10 //设置服务器标识符（1到10都可以）
log-bin = mysql_bin //开启二进制日志功能
[root@localhost ~]# systemctl restart mariadb //重启服务
新增数据
MariaDB [(none)]> create database school;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> use school;
Database changed
MariaDB [school]> create table student(id int not null primary key auto_increment,name varchar(50),age tinyint ,score float);
Query OK, 0 rows affected (0.008 sec)
MariaDB [school]> insert student(name,age) values ('tom',20),('jerry',15),('zhangshan',18);
Query OK, 3 rows affected (0.002 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [school]> select * from student;
+----+-----------+------+-------+
| id | name | age | score |
+----+-----------+------+-------+
| 1 | tom | 20 | NULL |
| 2 | jerry | 15 | NULL |
| 3 | zhangshan | 18 | NULL |
+----+-----------+------+-------+
3 rows in set (0.000 sec)
对数据库进行完全备份
[root@localhost ~]# mysqldump -uroot --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20210506.sql
[root@localhost ~]# ls
all-20210506.sql anaconda-ks.cfg
增加新内容
MariaDB [school]> insert student(name,age,score) value('lisi',19,85.5);
Query OK, 1 row affected (0.002 sec)
MariaDB [school]> update student set score=90 where name='tom';
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [school]> select * from student;
+----+-----------+------+-------+
| id | name | age | score |
+----+-----------+------+-------+
| 1 | tom | 20 | 90 |
| 2 | jerry | 15 | NULL |
| 3 | zhangshan | 18 | NULL |
| 4 | lisi | 19 | 85.5 |
+----+-----------+------+-------+
4 rows in set (0.000 sec)
3.4.2. mysql差异备份恢复
模拟误删数据
[root@localhost mysql]# mysql -e 'drop database school;'
[root@localhost mysql]# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
刷新创建新的二进制日志
[root@localhost mysql]# mysqladmin -uroot flush-logs
恢复完全备份
[root@localhost ~]# mysql -uroot < all-20210506.sql
[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 10.3.27-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
+--------------------+
4 rows in set (0.001 sec)
MariaDB [(none)]> use school;
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
MariaDB [school]> select * from student;
+----+-----------+------+-------+
| id | name | age | score |
+----+-----------+------+-------+
| 1 | tom | 20 | NULL |
| 2 | jerry | 15 | NULL |
| 3 | zhangshan | 18 | NULL |
+----+-----------+------+-------+
3 rows in set (0.000 sec)
恢复差异备份
//检查误删数据库的位置在什么地方
MariaDB [school]> show binlog events in 'mysql_bin.000002';
+------------------+-----+-------------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------------+-----------+-------------+--------------------------------------------------------------------+
| mysql_bin.000002 | 4 | Format_desc | 10 | 256 | Server ver: 10.3.27-MariaDB-log, Binlog ver: 4 |
| mysql_bin.000002 | 256 | Gtid_list | 10 | 285 | [] |
| mysql_bin.000002 | 285 | Binlog_checkpoint | 10 | 328 | mysql_bin.000001 |
| mysql_bin.000002 | 328 | Binlog_checkpoint | 10 | 371 | mysql_bin.000002 |
| mysql_bin.000002 | 371 | Gtid | 10 | 413 | BEGIN GTID 0-10-1 |
| mysql_bin.000002 | 413 | Intvar | 10 | 445 | INSERT_ID=4 |
| mysql_bin.000002 | 445 | Query | 10 | 566 | use `school`; insert student(name,age,score) value('lisi',19,85.5) |
| mysql_bin.000002 | 566 | Xid | 10 | 597 | COMMIT /* xid=460 */ |
| mysql_bin.000002 | 597 | Gtid | 10 | 639 | BEGIN GTID 0-10-2 |
| mysql_bin.000002 | 639 | Query | 10 | 752 | use `school`; update student set score=90 where name='tom' |
| mysql_bin.000002 | 752 | Xid | 10 | 783 | COMMIT /* xid=461 */ |
| mysql_bin.000002 | 783 | Gtid | 10 | 825 | GTID 0-10-3 |
| mysql_bin.000002 | 825 | Query | 10 | 914 | drop database school |
| mysql_bin.000002 | 914 | Rotate | 10 | 961 | mysql_bin.000003;pos=4 |
+------------------+-----+-------------------+-----------+-------------+--------------------------------------------------------------------+
14 rows in set (0.000 sec)
//使用mysqlbinlog恢复差异备份
```bash
[root@localhost ~]# mysqlbinlog --stop-position=825 /var/lib/mysql/mysql_bin.000002 |mysql -uroot
[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 10.3.27-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]> use school;
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
MariaDB [school]> select * from student;
+----+-----------+------+-------+
| id | name | age | score |
+----+-----------+------+-------+
| 1 | tom | 20 | 90 |
| 2 | jerry | 15 | NULL |
| 3 | zhangshan | 18 | NULL |
| 4 | lisi | 19 | 85.5 |
+----+-----------+------+-------+
4 rows in set (0.003 sec)
MariaDB [school]>
4.密码破解
//修改配置文件
[root@localhost ~]# vi /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[mysqld]
skip-grant-tables //跳过授权表，跳过权限
[root@localhost ~]# mysql //现在就可以无密码登录
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.27-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
+--------------------+
4 rows in set (0.001 sec)
//修改新密码
MariaDB [school]> select * from mysql.user\G
*************************** 1. row ***************************
Host: localhost
User: root
Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
Delete_history_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 //修改这个
password_expired: N
is_role: N
default_role:
max_statement_time: 0.000000
MariaDB [(none)]> update user set authentication_string = password('lixirong123!') where Host = 'localhost' and User = 'root'; //设置新的密码为lixirong123!
//修改配置文件
[root@localhost ~]# vi /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[mysqld]
skip-grant-tables //将这个删除，然后保存
//重启服务
[root@localhost ~]# systemctl restart mariadb
//用新密码登录
[root@localhost ~]# mysql -uroot -plixirong123!
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.27-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
总结步骤如下：
1、修改配置文件，增加skip-grant-tables
2、重启服务
3、进入数据库更新mysql.user表的authentication_string字段
4、修改配置文件，删除skip-grant-tables
5、重启服务，用新密码登录
323
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
