二进制格式mysql安装
[root@localhost ~]# cd /usr/src/[root@localhost src]#lsdebug kernels mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
[root@localhost src]# groupadd-r mysql
groupadd：“mysql”组已存在
[root@localhost src]# useradd-M -s /sbin/nologin -g mysql mysql
useradd：用户“mysql”已存在
[root@localhost src]#tar xf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz -C /usr/local/[root@localhost src]#ln -sv mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz mysql'mysql' -> 'mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz'
[root@localhost local]# chown -R mysql:mysql /usr/local/mysql
[root@localhost local]# ll -d /usr/local/mysql
lrwxrwxrwx. 1 mysql mysql 36 12月 30 05:01 /usr/local/mysql -> mysql-5.7.31-linux-glibc2.12-x86_64/
[root@localhost src]# ls /usr/local/mysql-5.7.31-linux-glibc2.12-x86_64/bin docs include lib LICENSEman README share support-files
[root@localhost src]#echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh[root@localhost src]# ./etc/profile.d/mysql.sh[root@localhost src]#echo$PATH/usr/local/mysql/bin:/usr/local/httpd/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@localhost src]#
[root@localhost ~]# mkdir /opt/data
[root@localhost~]# chown mysql:mysql /opt/data/[root@localhost~]# ll /opt/总用量0drwxr-xr-x. 2 mysql mysql 6 12月 29 00:56data
[root@localhost~]#
启动数据库
[root@localhost ~]# service mysqld start
Redirecting to/bin/systemctl start mysqld.service
初始化数据库
[root@localhost local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
2020-12-30T10:06:24.338513Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-12-30T10:06:24.759563Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-12-30T10:06:24.792643Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-12-30T10:06:24.852711Z 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: acb4ac01-4a86-11eb-b585-000c2975fe91.
2020-12-30T10:06:24.853487Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-12-30T10:06:25.543391Z 0 [Warning] CA certificate ca.pem is self signed.
2020-12-30T10:06:25.635377Z 1 [Note] A temporary password is generated for root@localhost: )9Ms#lXod+s=
[root@localhost local]#
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将无法正常处理连接请求
生成配置：
[root@localhost local]# vim /etc/my.cnf
[mysqld]
basedir= /usr/local/mysql
datadir= /opt/data
socket= /tmp/mysql.sock
port= 3306pid-file = /opt/data/mysql.pid
user=mysql
skip-name-resolve
启动：
[root@localhost local]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost local]# vim/etc/init.d/mysqld
basedir=/usr/local/mysql
datadir=/opt/data
启动mysql服务：
[root@localhost local]# mysql -uroot -p'y!=Qgsthp3hu'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 connectionid is 2Server version:5.7.31Copyright (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 clearthe current input statement.
mysql>
重设密码：
mysql> set password = password('123');
Query OK,0 rows affected, 1 warning (0.01sec)
mysql>quit
Bye
[root@localhost local]# chkconfig--add mysqld
[root@localhost local]# chkconfig mysqld on
[root@localhost local]# chkconfig--list
注：该输出结果只显示 SysV 服务，并不包含
原生 systemd 服务。SysV 配置数据
可能被原生 systemd 配置覆盖。
要列出 systemd 服务，请执行'systemctl list-unit-files'。
查看在具体 target 启用的服务请执行'systemctl list-dependencies [target]'。
mysqld0:关 1:关 2:开 3:开 4:开 5:开 6:关
[root@localhost local]#
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connectionid is 5Server version:5.7.31MySQL Community Server (GPL)
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 clearthe current input statement.
mysql>
备份方案特点
全量备份
全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。
数据恢复快。
备份时间长
增量备份
增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份
与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象
是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量
备份后所产生的增加和修改的文件，如此类推。
没有重复的备份数据
备份时间短
恢复数据时必须按一定的顺序进行
差异备份
备份上一次的完全备份后发生变化的所有文件。
差异备份是指在一次全备份后到进行差异备份的这段时间内
对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。
全量备份
[root@localhost ~]# mysql -uroot -p123
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 connectionid is 2Server version:5.7.31-log MySQL Community Server (GPL)
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 clearthe current input statement.
mysql>create database school;
Query OK,1 row affected (0.00sec)
mysql>use school
Database changed
mysql>use school;
Database changed
mysql> create table student(id int not null primary key auto_increment,name varchar(50),age tinyint);
Query OK,0 rows affected (0.12sec)
mysql> insert student(name,age)values('tom',10),('zhangshan',20),('lisi',30);
Query OK,3 rows affected (0.45sec)
Records:3 Duplicates: 0 Warnings: 0mysql>quit
Bye
[root@localhost ~]# mysqldump -uroot -p123 --all-databases > all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-202012310225.sql
all.sql
anaconda-ks.cfg
[root@localhost ~]# file all.sql
all.sql: UTF-8 Unicode text, with very long lines
[root@localhost ~]# mysql -uroot -pcsl123 -e 'drop database school;'mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost~]# mysql -uroot -pcsl123
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost~]# mysql -uroot -pcsl123 -e 'select * from school.student;'mysql: [Warning] Using a password on the command line interface can be insecure.+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | tom | 10 |
| 2 | zhangshan | 20 |
| 3 | lisi | 30 |
+----+----------+------+
在school查入数据
[root@localhost ~]# mysql
mysql>use school;
mysql> create table student1(id int not null primary key auto_increment,name varchar(20) not null,age tinyint);
mysql> insert student1(name,age) values('aa',13),('bb',14),('cc',22),('dd',33);
mysql> create table student2(id int not null primary key auto_increment,name varchar(20) not null,age tinyint);
mysql>show tables;+------------------+
| Tables_in_school |
+------------------+
| student |
| student1 |
| student2 |
+------------------+mysql> select *from student1;+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | aa | 13 |
| 2 | bb | 14 |
| 3 | cc | 22 |
| 4 | dd | 33 |
+----+----------+------+mysql>quit
[root@localhost~]# mysqldump -uroot school student1 >table_student1.sql
[root@localhost~]# mysqldump -uroot school >table_school.sql
mysql>create database information;
mysql>use information;
mysql> create table teacher(id int not null primary key auto_increment,name varchar(100)not null,age tinyint(4));
mysql> insert teacher(name,age) values('nmi',31),('hil',43),('alice',28);
mysql> exit
删除库school，使用备份文件table_school.sql恢复库school数据
mysql>drop database school;
mysql>create database school;
mysql>use school;
mysql> source table_school.sql;
差异备份
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
basedir= /usr/local/mysql
datadir= /opt/data
socket= /tmp/mysql.sock
port= 3306pid-file = /opt/data/mysql.pid
user=mysql
skip-name-resolve
skip-grant-tables
log-bin=mysql_bin
server-id=1[root@localhost~]# service mysqld restart
[root@localhost~]# rm -rf *.sql
[root@localhost~]# mysqldump -uroot -p123 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-202012291840.sql
mysql>use school;
mysql> insert student2(name,age) values('tom',21),('zhangshan',22),('lisi',23);
mysql> update student2 set age=21 where id=3;
mysql> drop database school;
[root@localhost ~]# ls /opt/data/auto.cnf client-key.pem ib_logfile1 mysql_bin.000002public_key.pem
ca-key.pem ib_buffer_pool ibtmp1 mysql_bin.index server-cert.pem
ca.pem ibdata1 localhost.localdomain.err mysql.pid server-key.pem
client-cert.pem ib_logfile0 mysql_bin.000001private_key.pem
[root@localhost~]# mysqladmin -uroot flush-logs
[root@localhost~]# ls /opt/data/auto.cnf client-key.pem ib_logfile1 mysql_bin.000002private_key.pem
ca-key.pem ib_buffer_pool ibtmp1 mysql_bin.000003public_key.pem
ca.pem ibdata1 localhost.localdomain.err mysql_bin.index server-cert.pem
client-cert.pem ib_logfile0 mysql_bin.000001 mysql.pid server-key.pem
[root@localhost~]# mysql -uroot -p123
all-202012310225.sql all.sql
[root@localhost~]# mysql -uroot -p123 < all-202012310225.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connectionid is 10Server version:5.7.31-log MySQL Community Server (GPL)
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 clearthe current input statement.
mysql> show binlog events in 'mysql_bin.000002'\G*************************** 1. row ***************************Log_name: mysql_bin.000002Pos:4Event_type: Format_desc
Server_id:1End_log_pos:123Info: Server ver:5.7.31-log, Binlog ver: 4
*************************** 2. row ***************************Log_name: mysql_bin.000002Pos:123Event_type: Previous_gtids
Server_id:1End_log_pos:154Info:*************************** 3. row ***************************Log_name: mysql_bin.000002Pos:154Event_type: Anonymous_Gtid
Server_id:1End_log_pos:219Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************Log_name: mysql_bin.000002Pos:219Event_type: Query
Server_id:1End_log_pos:319Info: create database school*************************** 5. row ***************************Log_name: mysql_bin.000002Pos:319Event_type: Anonymous_Gtid
Server_id:1End_log_pos:384Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 6. row ***************************Log_name: mysql_bin.000002Pos:384Event_type: Query
Server_id:1End_log_pos:555Info: use `school`; create table student(id int not null primary key auto_increment,name varchar(50),age tinyint)*************************** 7. row ***************************Log_name: mysql_bin.000002Pos:555Event_type: Anonymous_Gtid
Server_id:1End_log_pos:620Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 8. row ***************************Log_name: mysql_bin.000002Pos:620Event_type: Query
Server_id:1End_log_pos:694Info: BEGIN*************************** 9. row ***************************Log_name: mysql_bin.000002Pos:694Event_type: Table_map
Server_id:1End_log_pos:750Info: table_id:90(school.student)*************************** 10. row ***************************Log_name: mysql_bin.000002Pos:750Event_type: Write_rows
Server_id:1End_log_pos:822Info: table_id:90flags: STMT_END_F*************************** 11. row ***************************Log_name: mysql_bin.000002Pos:822Event_type: Xid
Server_id:1End_log_pos:853Info: COMMIT/*xid=11*/
*************************** 12. row ***************************Log_name: mysql_bin.000002Pos:853Event_type: Anonymous_Gtid
Server_id:1End_log_pos:918Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 13. row ***************************Log_name: mysql_bin.000002Pos:918Event_type: Query
Server_id:1End_log_pos:1016Info: drop database school*************************** 14. row ***************************Log_name: mysql_bin.000002Pos:1016Event_type: Rotate
Server_id:1End_log_pos:1063Info: mysql_bin.000003;pos=4
14 rows in set (0.00sec)
mysql>quit
Bye
[root@localhost~]# mysqlbinlog --stop-position=918 /opt/data/mysql_bin.000002|mysql -uroot -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
安装这个包
[root@localhost ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.21/binary/redhat/8/x86_64/Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar
[root@localhost ~]# tar xf Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar[root@localhost~]# yum -y install percona*
使用innobackupex命令全量备份
[root@localhost ~]# innobackupex --user=root --password=123 --host=192.168.122.134 /backups/xtrabackup: recognized server arguments:--datadir=/opt/data --log_bin=mysql_bin --server-id=1xtrabackup: recognized client arguments:210103 02:04:05innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints"completed OK!".210103 02:04:05 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=192.168.122.134' as 'root'(using password: YES).210103 02:04:05version_check Connected to MySQL server210103 02:04:05version_check Executing a version check against the server...
DBD::mysql::db selectall_hashref failed: Table'performance_schema.session_variables' doesn't exist at - line 1221.
[root@localhost backups]# ll
总用量0drwxr-x---. 2 root root 6 1月 3 02:04 2021-01-03_02-04-05[root@localhost backups]# ll2021-01-03_02-04-05/total13450
-rw-r-----. 1 root root 487 Dec 29 21:41 backup-my.cnf-rw-r-----. 1 root root 848 Dec 29 21:41ib_buffer_pool-rw-r-----. 1 root root 12582912 Dec 29 21:41ibdata1
drwxr-x---. 2 root root 58 Dec 29 21:41information
drwxr-x---. 2 root root 4096 Dec 29 21:41mysql
drwxr-x---. 2 root root 8192 Dec 29 21:41performance_schema
drwxr-x---. 2 root root 138 Dec 29 21:41school
drwxr-x---. 2 root root 8192 Dec 29 21:41 sys
删除数据库再恢复
[root@localhost backups]# /etc/init.d/mysqld stop
[root@localhost backups]# innobackupex--apply-log /backups/2021-01-03_02-04-05/[root@localhost backups]#rm -rf /opt/data/[root@localhost backups]# innobackupex--copy-back /backups/2020-12-29_21-41-12/
201229 22:19:15 completed OK![root@localhost backups]# ll/opt/data/total132925
-rw-r-----. 1 root root 848 Dec 29 22:19ib_buffer_pool-rw-r-----. 1 root root 12582912 Dec 29 22:19ibdata1-rw-r-----. 1 root root 50331648 Dec 29 22:19ib_logfile0-rw-r-----. 1 root root 50331648 Dec 29 22:19ib_logfile1-rw-r-----. 1 root root 12582912 Dec 29 22:19ibtmp1
drwxr-x---. 2 root root 58 Dec 29 22:19information
drwxr-x---. 2 root root 4096 Dec 29 22:19mysql
drwxr-x---. 2 root root 8192 Dec 29 22:19performance_schema
drwxr-x---. 2 root root 138 Dec 29 22:19school
drwxr-x---. 2 root root 8192 Dec 29 22:19sys-rw-r-----. 1 root root 24 Dec 29 22:19xtrabackup_binlog_pos_innodb-rw-r-----. 1 root root 489 Dec 29 22:19xtrabackup_info-rw-r-----. 1 root root 1 Dec 29 22:19xtrabackup_master_key_id
[root@localhost backups]#chown -R mysql.mysql /opt/data/[root@localhost backups]#/etc/init.d/mysqld start