linux服务-mysql进阶

1.二进制格式安装

mysql二进制包下载路径

优点：解压后配置完成即可使用

//下载包
[root@localhost ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz

//验证md5码一致性
[root@localhost ~]# md5sum mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz 
bf1a97232dbab450cf73356fc74a02a3  mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz

//解压到指定路径
[root@localhost ~]# tar -xf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz -C /usr/local/

//重命名或创建软链接
[root@localhost local]# mv mysql-5.7.31-linux-glibc2.12-x86_64/ mysql

//创建root用户和组mysql
[root@localhost ~]# groupadd -r mysql
[root@localhost ~]# useradd -M -s /usr/sbin/nologin -g mysql mysql

//修改目录属主组为mysql
[root@localhost home]# chmod -R mysql.mysql /usr/local/mysql

//添加环境变量
编辑profile文件
[root@localhost ~]# vim /etc/profile
...
export PATH=$PATH:/usr/local/mysql/bin
或输出重定向至脚本文件mysql.sh
[root@localhost ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@localhost ~]# . /etc/profile.d/mysql.sh 
[root@localhost ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/usr/local/apache/bin/:/root/bin

//建立软链接
[root@localhost ~]# ln -s /usr/local/mysql/include/ /usr/include/mysql

//添加帮助文档路径
[root@localhost ~]# vi /etc/man_db.conf 
#MANDATORY_MANPATH                      /usr/src/pvm3/man
#
...
MANDATORY_MANPATH                       /usr/local/mysql/man

//添加库文件路径
[root@localhost ~]# vi /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib
[root@localhost ~]# ldconfig 

//建立数据存放目录
[root@localhost ~]# chown -R mysql.mysql /opt/data
[root@localhost ~]# ll /opt/
total 0
drwxr-xr-x. 2 mysql mysql 6 Dec 28 18:41 data

//编辑配置文件my.cnf
[root@localhost ~]# vim  /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 ~]# mysqld --initialize --user=mysql --datadir=/opt/data/
...
2020-12-28T10:45:21.829771Z 1 [Note] A temporary password is generated for root@localhost: l?v:DA*C5-w7//临时密码（随机生成）
* 建议echo至空文件

//复制到etc/init.d/的mysqld
[root@localhost ~]# cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld
//配置控制启动脚本
[root@localhost ~]# vim /etc/init.d/mysqld
# overwritten by settings in the MySQL configuration files.
...
basedir=/usr/local/mysql
datadir=/opt/data
//启动服务
[root@localhost ~]# service mysqld start 
Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'.
 SUCCESS! 
//针对缺少库文件libncurses.so.5无法启动的情况
[root@localhost ~]# yum -y install libncurses*



//重置密码
[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.7.31
...

mysql> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

2.MySQL配置文件

• 存放路径/etc/my.cnf
• 关于.my.cnf:存放于家目录，多应用于脚本，不会输出登录步骤

[root@localhost ~]# vim .my.cnf
[client]
user=root
password=123456

配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效

/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf

参数	说明
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将无法正常处理连接请求

• 数据库破解密码

//配置行增加“跳过授权表”
[root@localhost ~]# vim /etc/my.cnf
...
skip-grant-tables

//重启服务
[root@localhost ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

//进入‘mysql’数据库，看‘user’表
mysql> select * from userG;

//update命令改需要更改的用户
mysql> update user set authentication_string=password('re123456!') where User='root' and Host='localhost'
    -> ;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

//配置文件中删除skip-grant-tables
//重启服务
[root@localhost ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

//登录验证
[root@localhost ~]# mysql -uroot -pre123456!
...
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> 

3.MYSQL备份

3.1备份方案

备份方案	优缺点
全量备份	在某个时间点的数据进行完整拷贝， 数据恢复快，备份时间长
增量备份	针对上一次改变的数据进行备份，还原需要每一次的数据， 备份时间断，但条件苛刻，需按一定顺序
差异备份	指第一次全量备份后所有改动的文件备份，我们只需对全量和差异备份进行恢复即可

主要方案：全量+差异

3.2 常用备份工具

• mysqldump
  • -u:指定用户
  • -h指定主机
  • -p指定密码
  • --databases [OPTIONS] DB1 [DB2 DB3...]指定备份一个或多个数据库
  • --all-databases [OPTIONS]全数据库
  • database [TABLES]指定数据库的指定表

3.2.1全量备份

备份操作

//对全数据库进行备份
[root@localhost ~]# mysqldump -uroot -pre123456! --all-databases > all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
//对特定数据库的特定表备份
[root@fangxinxin ~]# mysqldump -uroot -p're123456!' fxx student> table_student.mysql
//对特定数据库备份
[root@fangxinxin ~]# mysqldump -uroot -p --databases company> database_company.sql

还原操作

• 对指定库指定表恢复备份

mysql> drop table student;//预删除学生表
Query OK, 0 rows affected (0.00 sec)
//读取备份文件
mysql> source table_student.mysql
Query OK, 0 rows affected (0.00 sec)

...

Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | zhangshan   |   26 |
|  6 | lisi        |   50 |
|  7 | chenshuo    |   10 |
|  8 | wangwu      |  100 |
|  9 | qiuyi       |   15 |
| 10 | qiuxiaotian |   20 |
+----+-------------+------+
9 rows in set (0.00 sec)

• 恢复全库

//模拟删除
mysql> drop database company;
Query OK, 1 row affected (0.04 sec)
mysql> drop database fxx;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
//将备份数据输入重定向至mysql中
[root@fangxinxin ~]# mysql -uroot -p're123456!'< all.sql 
//查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| company            |
| fxx                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

3.2.2 差异备份

• 启用二进制日志文件

[root@fangxinxin ~]# vi /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

server-id=1  //设置服务器标识
log-bin=mysql_bin //开启二进制服务功能
//重启服务
[root@fangxinxin ~]# systemctl restart mysqld.service 
//看已创建表
mysql> use school;
Database changed
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
| teacher          |
+------------------+
2 rows in set (0.00 sec)

mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | a    |   16 |
|  2 | b    |   17 |
|  3 | c    |   12 |
+----+------+------+
3 rows in set (0.00 sec)

mysql> select * from teacher;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  1 | m    |   33 |   3002 |
|  2 | c    |   17 |   5388 |
|  3 | r    |   28 |   2253 |
+----+------+------+--------+
3 rows in set (0.00 sec)
//全量备份
[root@fangxinxin ~]# mysqldump -uroot -pre123456! --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs  > all-20201230.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.
//增加新记录
mysql> insert student(name,age) values('tom',17),('eason',26),('leon',18);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | a     |   16 |
|  2 | b     |   17 |
|  3 | c     |   12 |
|  4 | tom   |   17 |
|  5 | eason |   26 |
|  6 | leon  |   18 |
+----+-------+------+
6 rows in set (0.00 sec)
mysql> update student set age=13 where name='tom';
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
...
|  4 | tom   |   13 |

• mysql差异备份恢复

//模拟误删school数据库
mysql> drop database school;
Query OK, 2 rows affected (0.01 sec)
//刷新二进制日志文件
[root@fangxinxin ~]# mysqladmin -uroot -pre123456!  flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.、
//查看
[root@fangxinxin ~]# ll /opt/data/
...
-rw-r-----. 1 mysql mysql      154 Dec 30 23:51 mysql_bin.000003
//恢复全备
[root@fangxinxin ~]# mysql -uroot -pre123456!  < all-20201230.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@fangxinxin ~]# mysql -uroot -pre123456!  -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| company            |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
//查看日志文件的位置
mysql> show binlog events in 'mysql_bin.000003'G;
...
*************************** 428. row ***************************
   Log_name: mysql_bin.000003
        Pos: 845040
 Event_type: Query
  Server_id: 1
End_log_pos: 845163
       Info: use `school`; /*!40000 ALTER TABLE `teacher` ENABLE KEYS */
428 rows in set (0.00 sec)
//mysqlbinlog命令恢复
[root@fangxinxin ~]# mysqlbinlog --stop-position=845040 /opt/data/mysql_bin.000003 |mysql -uroot -pre123456!
mysql: [Warning] Using a password on the command line interface can be insecure.