mysql 不支持jsontable MySQL 不支持minus

二进制包安装mysql

[root@localhost ~]# cd /usr/src
[root@localhost src]# ls
debug  kernels  mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

[root@localhost src]# tar -xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost src]# cd /usr/local/
[root@localhost local]# ls
bin  games    lib    libexec                              sbin   src
etc  include  lib64  mysql-5.7.22-linux-glibc2.12-x86_64  share

• 新建mysql系统用户，并将mysql目录文件更改用户及组

[root@localhost local]# useradd -r -M -s /sbin/nologin mysql
[root@localhost local]# chown -R mysql.mysql mysql*
[root@localhost local]# ll
lrwxrwxrwx. 1 mysql mysql  36 Oct 22 17:01 mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 mysql mysql 129 Oct 22 17:01 
......

• 新建mysql数据库数据存放目录，更改此文件所属用户及组

[root@localhost mysql]# mkdir /var/mysql_data;chown -R mysql.mysql /var/mysql_data
[root@localhost mysql]# ll -d /var/mysql_data
drwxr-xr-x. 2 mysql mysql 6 Oct 22 17:06 /var/mysql_data

[root@localhost mysql]# echo 'export PATH=$PATH:/usr/local/mysql/bin' > /etc/
[root@localhost mysql]# source /etc/ 
[root@localhost mysql]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin

[root@localhost mysql]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/var/mysql_data/
2020-10-22T09:10:19.831335Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-10-22T09:10:21.859987Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-10-22T09:10:22.075321Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-10-22T09:10:22.162614Z 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: 69e222bd-1446-11eb-832e-000c291ef71e.
2020-10-22T09:10:22.196056Z 0 [Warning] Gtid table is not ready to be used. Table '' cannot be opened.
2020-10-22T09:10:22.197776Z 1 [Note] A temporary password is generated for root@localhost: -=/V:0#Bs9we   ## 系统将提供临时密码，用于第一次登录，登录后修改密码即可

[root@localhost mysql]# echo '-=/V:0#Bs9we' > ~/.passwd
[root@localhost mysql]# cat ~/.passwd 
-=/V:0#Bs9we

[root@localhost mysql]# vim /etc/
[mysqld]
basedir = /usr/local/mysql   ## 主程序存放位置
datadir = /var/mysql_data    ## 数据库文件存放位置
socket = /tmp/mysql.sock     ## 所用套接字文件
port = 3306                  ## 默认端口号
pid-file = /var/mysql_data/mysql.pid     ## pid文件
user = mysql                             ## 服务所属用户名
skip-name-resolve                        ## 跳过域名解析。只允许ip方式登录

• 编辑服务启动文件，将程序位置及文件存放位置的目录补全,且将文件复制到文件下，用于service管理

[root@localhost mysql]# vim support-files/mysql.server
......
basedir=/usr/local/mysql
datadir=/var/mysql_data
......
[root@localhost mysql]# cp support-files/mysql.server /etc//mysqld

[root@localhost mysql]# service mysqld start
Starting MySQL.Logging to '/var/mysql_data/'.
 SUCCESS!

[root@localhost mysql]# ps -ef |grep mysqld
root      11175      1  0 17:15 pts/3    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/var/mysql_data --pid-file=/var/mysql_data/mysql.pid
mysql     11363  11175  0 17:15 pts/3    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/var/mysql_data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error= --pid-file=/var/mysql_data/mysql.pid --socket=/tmp/mysql.sock --port=3306
root      11400   2621  0 17:17 pts/3    00:00:00 grep --color=auto mysqld

[root@localhost mysql]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.22

Copyright (c) 2000, 2018, 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> 


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

[root@localhost mysql]# mysql -uroot -p'123456'
......

mysql>

[root@localhost mysql]# chkconfig --add mysqld
[root@localhost mysql]# chkconfig --list

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

mysqld         	0:off	1:off	2:on	3:on	4:on	5:on	6:off

mysql数据备份及恢复

备份方案

全量备份

全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。
数据恢复快。备份时间长

完全备份的好处是数据恢复方便，因为所有的数据都在同一个备份中，所以只要恢复完全备份，所有的数据都会被恢复。如果完全备份备份的是整块硬盘，那么甚至不需要数据恢复，只要把备份硬盘安装上，服务器就会恢复正常.

增量备份

完全备份随着数据量的加大，备份耗费的时间和占用的空间会越来越多，所以完全备份不会也不能每天进行，这时增量备份的作用就体现了出来。
增量备份是指先进行一次完全备份，服务器运行一段时间之后，比较当前系统和完全备份的备份数据之间的差异，只备份有差异的数据.这就是增量备份

差异备份

差异备份也要先进行一次完全备份，但是和增量备份不同的是，每次差异备份都备份和原始的完全备份不同的数据

相比较而言，差异备份既不像完全备份一样把所有数据都进行备份，也不像增量备份在进行数据恢复时那么麻烦，只要先恢复完全备份的数据，再恢复差异备份的数据即可

全量备份与恢复

mysqldump [OPTIONS] database [tables ...]
mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

mysqldump对整个数据库进行备份

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sawyer             |
| sy                 |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

[root@localhost mysql]# mysqldump --all-databases > ~/all_backup

[root@localhost ~]# ll all_backup 
-rw-r--r--. 1 root root 803238 Oct 23 15:08 all_backup

对单个数据库的指定表进行备份

mysql> show tables;
+------------------+
| Tables_in_sawyer |
+------------------+
| nation_list      |
| student1         |
+------------------+
2 rows in set (0.00 sec)


# 将sawyer数据库中的两张表进行备份到一个文件中


[root@localhost ~]# mysqldump sawyer student1 nation_list > 231518_backup
[root@localhost ~]# ll 231518_backup 
-rw-r--r--. 1 root root 2637 Oct 23 15:18 231518_backup

全量备份的数据恢复

1.将sy库删除掉

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sawyer             |
| sy                 |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> drop database sy;
Query OK, 1 row affected (0.00 sec)

2.在mysql交互中进行恢复

mysql> source all_backup;

......

## sy库已经恢复
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sawyer             |
| sy                 |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

1. 将sawyer库中的nation_list删除掉

mysql> show tables;
+------------------+
| Tables_in_sawyer |
+------------------+
| nation_list      |
| student1         |
+------------------+
2 rows in set (0.00 sec)

mysql> drop table nation_list;
Query OK, 0 rows affected (0.00 sec)

2.在shell交互中进行恢复nation_list

[root@localhost ~]# mysql sawyer < 231518_backup    # 指定库为sawyer


## nation_list已经恢复

[root@localhost ~]# mysql -e'show tables from sawyer'
+------------------+
| Tables_in_sawyer |
+------------------+
| nation_list      |
| student1         |
+------------------+

注意:在shell中恢复指定表时，需要指定库名。如果是恢复数据库则不需要指定

差异备份与恢复

mysqldump差异备份

现有sawyer库中的两张表

1. nation_list

2. student1

[root@localhost ~]# vim /etc/

[mysqld]
basedir=/usr/local/mysql
datadir=/var/mysql_data
socket=/tmp/mysql.sock
port=3306
pid-file=/var/mysql_data/mysql.pid
user=mysql
skip-name-resolve

server-id=1       ## 指定日志文件中的server-id
log-bin=mysql_bin  ## 开启二进制日志功能

[client]
port=3306
user=root
password=123456

[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

[root@localhost mysql_data]# mysqldump --single-transaction --flush-logs --master-data=2 sawyer student1 nation_list --delete-master-logs > ~/

[root@localhost mysql_data]# ll ~/ 
-rw-r--r--. 1 root root 2789 Oct 23 15:56 /root/

## 修改ID为1的age字段的内容
mysql> select * from student1;
+----+-----+
| id | age |
+----+-----+
|  1 |  20 |
|  2 |  34 |
|  3 |  19 |
|  4 |  50 |
+----+-----+
4 rows in set (0.00 sec)

mysql> update student1 set age = 30 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

## 删除掉nation_list这张表

mysql> drop table nation_list;
Query OK, 0 rows affected (0.00 sec)

[root@localhost mysql_data]# mysqladmin flush-logs  新建新编号的日志文件


[root@localhost mysql_data]# cat mysql_bin.index 
./
./mysql_bin.000003   #目前日志将存放于00003日志文件中

利用差异备份恢复数据

[root@localhost ~]# ls
231518_backup    all_backup  anaconda-ks.cfg
[root@localhost ~]# mysql sawyer <

• 查看全量备份之后操作的日志文件，查看想要恢复的pos

mysql> show binlog events in ''\G

......
*************************** 9. row ***************************
   Log_name: 
        Pos: 491                  ##491为删除表之前的pos值
 Event_type: Query
  Server_id: 1
End_log_pos: 619
       Info: use `sawyer`; DROP TABLE `nation_list` /* generated by server */
*************************** 10. row ***************************
   Log_name: 
        Pos: 619
 Event_type: Rotate
  Server_id: 1
End_log_pos: 666
       Info: mysql_bin.000003;pos=4
10 rows in set (0.00 sec)

[root@localhost mysql_data]# mysqlbinlog --stop-position=491 /var/mysql_data/ | mysql

mysql> use sawyer
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> show tables;
+------------------+
| Tables_in_sawyer |
+------------------+
| nation_list      |
| student1         |
+------------------+
2 rows in set (0.01 sec)



## 查看修改的年龄内容

mysql> select * from student1;
+----+-----+
| id | age |
+----+-----+
|  1 |  30 |
|  2 |  34 |
|  3 |  19 |
|  4 |  50 |
+----+-----+
4 rows in set (0.00 sec)