1.MySQL二进制安装
//下载二进制格式的mysql软件包
wget https://downloads.mysql.com/archives/get/file/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
//创建用户和组
[root@localhost ~]# groupadd -r mysql
[root@localhost ~]# useradd -r -M -s /sbin/nologin -g mysql mysql
//解压软件至/usr/local/
anaconda-ks.cfg mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
[root@localhost ~]# tar xf mysql-5.7.30-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.30-linux-glibc2.12-x86_64 share
[root@localhost ~]# cd /usr/local/
[root@localhost local]# ln -sv mysql-5.7.30-linux-glibc2.12-x86_64/ mysql
"mysql" -> "mysql-5.7.30-linux-glibc2.12-x86_64/"
[root@localhost local]# ll
总用量 0
drwxr-xr-x. 2 root root 6 11月 5 2016 bin
drwxr-xr-x. 2 root root 6 11月 5 2016 etc
drwxr-xr-x. 2 root root 6 11月 5 2016 games
drwxr-xr-x. 2 root root 6 11月 5 2016 include
drwxr-xr-x. 2 root root 6 11月 5 2016 lib
drwxr-xr-x. 2 root root 6 11月 5 2016 lib64
drwxr-xr-x. 2 root root 6 11月 5 2016 libexec
lrwxrwxrwx. 1 root root 36 6月 10 20:54 mysql -> mysql-5.7.30-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 6月 10 20:52 mysql-5.7.30-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 11月 5 2016 sbin
drwxr-xr-x. 5 root root 49 5月 22 11:26 share
drwxr-xr-x. 2 root root 6 11月 5 2016 src
[root@localhost local]#
//修改目录/usr/local/mysql的属主属组
[root@localhost ~]# chown -R mysql.mysql /usr/local/mysql
[root@localhost ~]# ll /usr/local/mysql
lrwxrwxrwx. 1 mysql mysql 36 6月 10 20:54 /usr/local/mysql -> mysql-5.7.30-linux-glibc2.12-x86_64/
//添加环境变量
[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:/root/bin
//建立数据存放目录
[root@localhost ~]# mkdir /mysqldata
[root@localhost ~]# chown -R mysql.mysql /mysqldata
//初始化数据库
[root@localhost ~]# yum -y install libaio-devel
已加载插件:fastestmirror
Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
base | 3.6 kB 00:00:00
extras | 2.9 kB 00:00:00
updates | 2.9 kB 00:00:00
updates/7/x86_64/primary_db | 2.1 MB 00:00:00
Determining fastest mirrors
* base: mirrors.cqu.edu.cn
* extras: mirrors.cqu.edu.cn
* updates: mirrors.cqu.edu.cn
正在解决依赖关系
--> 正在检查事务
---> 软件包 libaio-devel.x86_64.0.0.3.109-13.el7 将被 安装
--> 解决依赖关系完成
依赖关系解决
=====================================================================================
Package 架构 版本 源 大小
=====================================================================================
正在安装:
libaio-devel x86_64 0.3.109-13.el7 base 13 k
事务概要
=====================================================================================
安装 1 软件包
总下载量:13 k
安装大小:7.8 k
Downloading packages:
libaio-devel-0.3.109-13.el7.x86_64.rpm | 13 kB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
正在安装 : libaio-devel-0.3.109-13.el7.x86_64 1/1
验证中 : libaio-devel-0.3.109-13.el7.x86_64 1/1
已安装:
libaio-devel.x86_64 0:0.3.109-13.el7
完毕!
[root@localhost ~]# mysqld --initialize --user=mysql --datadir=/mysqldata
2020-06-10T13:07:06.847893Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-06-10T13:07:09.483667Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-06-10T13:07:09.523170Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-06-10T13:07:09.583742Z 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: 4ad00e95-ab1b-11ea-ae9f-000c2954d73b.
2020-06-10T13:07:09.584753Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-06-10T13:07:11.023500Z 0 [Warning] CA certificate ca.pem is self signed.
2020-06-10T13:07:11.230774Z 1 [Note] A temporary password is generated for root@localhost: f*Ua0:f0MFd0
[root@localhost ~]#
//生成配置文件
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/mysqldata
socket=/tmp/mysql.sock
port=3306
pid-file=/mysqldata/mysql.pid
user=mysql
skip-name-resolve
//配置服务启动脚本
[root@localhost ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost ~]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@localhost ~]# sed -ri 's#^(datadir=).*#\1/mysqldata#g' /etc/init.d/mysqld
[root@localhost ~]#
或
[root@localhost ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost ~]# vim /etc/init.d/mysqld
修改前
basedir=
datadir=
修改后
basedir=/usr/local/mysql
datadir=/mysqldata
//启动mysql并设置开机自启
[root@localhost ~]# service mysqld start
Unit mysqld.service could not be found.
Starting MySQL. SUCCESS!
[root@localhost ~]# ss -ant
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
ESTAB 0 80 192.168.20.100:22 192.168.20.1:52636
LISTEN 0 80 :::3306 :::*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
[root@localhost ~]# chkconfig mysqld on
//修改密码使用临时密码登录
[root@localhost ~]# mysql -uroot -p'f*Ua0:f0MFd0'
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.30
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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('zzl123');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql配置文件
mysql的配置文件为/etc/my.cnf
配置文件查找次序:若在多个配置文件中均有设定,则最后找到的最终生效
mysql常用配置文件参数:
参数 | 说明 |
---|---|
port = 3306 | 监听端口号 |
basedir = /usr/local/mysql | 指定MySQL的安装路径 |
datadir = /mysqldata | 指定MySQL的数据存放路径 |
socket = /tmo/mysql.sock | 指定套接字文件位置 |
pid-file = /mysqldata/mysql.pid | 指定进程ID文件存放路径 |
user = mysql | 指定MySQL以什么用户的身份服务 |
skip-name-resolve | 跳过DNS解析的时间 |
3. mysql数据库备份与恢复
3.1 数据库常用备份方案
数据库备份方案:
- 全量备份
- 增量备份
- 差异备份
备份方案 | 特点 |
---|---|
全量备份 | 对某一个时间点的所有数据完全拷贝 数据恢复快,备份时间长 |
增量备份 | 增加上一次备份后的增加的和修改的数据,第一次必须是全备 恢复必须按顺序恢复 没有重复的备份数据 备份时间短 恢复数据慢 |
差异备份 | 备份完全备份后发生变化的所有文件 行恢复时,只需对第一次全量备份和最后一次差异备份进行恢复 |
- 数据量少时使用全量备份
- 数据量较多结合全量备份和差异备份使用
3.2 mysql备份工具mysqldump
//全量备份
[root@localhost ~]# mysqldump -uroot -pzzl123 --all-databases > all-202006121024.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]#
\\恢复数据
[root@localhost ~]# mysql < all-202006121024.sql
\\备份
[root@localhost ~]# mysqldump -uroot -pzzl123 student > sudent-202006121039.sql
3.4 差异备份与恢复
3.4.1. mysql差异备份
开启MySQL服务器的二进制日志功能
[mysql]
basedir = /usr/local/mysql
datadir = /mysqldata
socket = /tmp/mysql.sock
port = 3306
pid-file = /mysqldata/mysql.pid
user = mysql
skip-name-resolve
server-id = 10 //设置服务标识
log-bin = mysql.bin //开启二进制功能
对数据库进行完全备份
[root@localhost ~]# [root@localhost ~]# mysqldump --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-202006121059.sql
mysql> use zzl;
Database changed
mysql> select * from student;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 15 |
| 2 | zhangshan | 20 |
| 3 | jerry | 5 |
+----+-----------+------+
3 rows in set (0.00 sec)
mysql> insert student(name,age) value('jieke',20);
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 15 |
| 2 | zhangshan | 20 |
| 3 | jerry | 5 |
| 4 | jieke | 20 |
+----+-----------+------+
4 rows in set (0.00 sec)
mysql> update student set age = 23 where name = 'jerry' ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
3.4.2. mysql差异备份恢复
//模拟数据库误删
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zzl |
+--------------------+
5 rows in set (0.00 sec)
mysql> drop database zzl;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> quit
刷新创建新的二进制日志
[root@localhost ~]# ll /mysqldata/
总用量 122980
-rw-r-----. 1 mysql mysql 56 6月 12 11:40 auto.cnf
-rw-------. 1 mysql mysql 1680 6月 12 11:40 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 6月 12 11:40 ca.pem
-rw-r--r--. 1 mysql mysql 1112 6月 12 11:40 client-cert.pem
-rw-------. 1 mysql mysql 1680 6月 12 11:40 client-key.pem
-rw-r-----. 1 mysql mysql 341 6月 12 12:04 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 6月 12 12:05 ibdata1
-rw-r-----. 1 mysql mysql 50331648 6月 12 12:05 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 6月 12 11:40 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 6月 12 12:05 ibtmp1
-rw-r-----. 1 mysql mysql 17732 6月 12 12:05 localhost.localdomain.err
drwxr-x---. 2 mysql mysql 4096 6月 12 11:40 mysql
-rw-r-----. 1 mysql mysql 154 6月 12 12:05 mysql.000002
-rw-r-----. 1 mysql mysql 15 6月 12 12:05 mysql.index
-rw-r-----. 1 mysql mysql 6 6月 12 12:05 mysql.pid
drwxr-x---. 2 mysql mysql 8192 6月 12 11:40 performance_schema
-rw-------. 1 mysql mysql 1680 6月 12 11:40 private_key.pem
-rw-r--r--. 1 mysql mysql 452 6月 12 11:40 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 6月 12 11:40 server-cert.pem
-rw-------. 1 mysql mysql 1680 6月 12 11:40 server-key.pem
drwxr-x---. 2 mysql mysql 8192 6月 12 11:40 sys
drwxr-x---. 2 mysql mysql 58 6月 12 11:56 zzl
[root@localhost ~]# mysqladmin -uroot -p'zzl123' flush-logs
[root@localhost ~]# ll /mysqldata/
总用量 123796
-rw-r-----. 1 mysql mysql 56 6月 12 11:40 auto.cnf
-rw-------. 1 mysql mysql 1680 6月 12 11:40 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 6月 12 11:40 ca.pem
-rw-r--r--. 1 mysql mysql 1112 6月 12 11:40 client-cert.pem
-rw-------. 1 mysql mysql 1680 6月 12 11:40 client-key.pem
-rw-r-----. 1 mysql mysql 341 6月 12 12:04 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 6月 12 12:15 ibdata1
-rw-r-----. 1 mysql mysql 50331648 6月 12 12:15 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 6月 12 11:40 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 6月 12 12:10 ibtmp1
-rw-r-----. 1 mysql mysql 17732 6月 12 12:05 localhost.localdomain.err
drwxr-x---. 2 mysql mysql 4096 6月 12 12:15 mysql
-rw-r-----. 1 mysql mysql 832706 6月 12 12:18 mysql.000002
-rw-r-----. 1 mysql mysql 154 6月 12 12:18 mysql.000003
-rw-r-----. 1 mysql mysql 30 6月 12 12:18 mysql.index
-rw-r-----. 1 mysql mysql 6 6月 12 12:05 mysql.pid
drwxr-x---. 2 mysql mysql 8192 6月 12 11:40 performance_schema
-rw-------. 1 mysql mysql 1680 6月 12 11:40 private_key.pem
-rw-r--r--. 1 mysql mysql 452 6月 12 11:40 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 6月 12 11:40 server-cert.pem
-rw-------. 1 mysql mysql 1680 6月 12 11:40 server-key.pem
drwxr-x---. 2 mysql mysql 8192 6月 12 11:40 sys
drwxr-x---. 2 mysql mysql 58 6月 12 12:15 zzl
[root@localhost ~]#
恢复完全备份
[root@localhost ~]# mysql < all-202006121059.sql
mysql> show binlog events in 'mysql.000003';
+--------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+--------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql.000003 | 4 | Format_desc | 10 | 123 | Server ver: 5.7.30-log, Binlog ver: 4 |
| mysql.000003 | 123 | Previous_gtids | 10 | 154 | |
| mysql.000003 | 154 | Anonymous_Gtid | 10 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql.000003 | 219 | Query | 10 | 290 | BEGIN |
| mysql.000003 | 290 | Table_map | 10 | 343 | table_id: 168 (zzl.student) |
| mysql.000003 | 343 | Write_rows | 10 | 390 | table_id: 168 flags: STMT_END_F |
| mysql.000003 | 390 | Xid | 10 | 421 | COMMIT /* xid=800 */ |
| mysql.000003 | 421 | Anonymous_Gtid | 10 | 486 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql.000003 | 486 | Query | 10 | 557 | BEGIN |
| mysql.000003 | 557 | Table_map | 10 | 610 | table_id: 168 (zzl.student) |
| mysql.000003 | 610 | Update_rows | 10 | 666 | table_id: 168 flags: STMT_END_F |
| mysql.000003 | 666 | Xid | 10 | 697 | COMMIT /* xid=801 */ |
| mysql.000003 | 697 | Anonymous_Gtid | 10 | 762 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql.000003 | 762 | Query | 10 | 851 | drop database zzl |
| mysql.000003 | 851 | Rotate | 10 | 894 | mysql.000004;pos=4 |
+--------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)
mysql> quit
//使用mysqlbinlog恢复差异备份
[root@localhost ~]# mysqlbinlog --stop-position=762 /mysqldata/mysql.000003 | mysql
mysql> use zzl;
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> select * from student;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 18 |
| 2 | jerry | 20 |
| 3 | zhangshan | 20 |
| 4 | jieke | 20 |
+----+-----------+------+
4 rows in set (0.00 sec)
mysql>