linux服务-mysql进阶
1.二进制格式安装
优点：解压后配置完成即可使用
//下载包
[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 user\G;
//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.
