二进制格式安装MySQL和MySQL备份

二进制格式安装MySQL

下载安装包

[root@localhost ~]# dnf -y install wget		//下载安装包需要的命令
[root@localhost ~]# wget -c  https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz		//下载MySQL二进制格式安装包
//下载过程省略
[root@localhost ~]# ls
anaconda-ks.cfg  mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz

创建用户

[root@localhost ~]# useradd -r -M -s /sbin/nologin mysql

解压到指定文件

[root@localhost ~]# tar xf mysql-5.7.33-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.33-linux-glibc2.12-x86_64  share

创建软连接

[root@localhost ~]# ln -s /usr/local/mysql-5.7.33-linux-glibc2.12-x86_64/ /usr/local/mysql
[root@localhost ~]# ll /usr/local/
total 0
......
lrwxrwxrwx. 1 root root  47 May  6 10:58 mysql -> /usr/local/mysql-5.7.33-linux-glibc2.12-x86_64/
......

修改用户属主和属组

[root@localhost ~]# chown -R mysql.mysql /usr/local/mysql*
[root@localhost ~]# ll /usr/local/
......
lrwxrwxrwx. 1 mysql mysql  47 May  6 10:58 mysql -> /usr/local/mysql-5.7.33-linux-glibc2.12-x86_64/
......

添加环境变量

[root@localhost ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@localhost ~]# source /etc/profile.d/mysql.sh 
[root@localhost ~]# which mysql
/usr/local/mysql/bin/mysql

创建存放数据的目录并修改属主属组

• 数据库的数据量比较大，所以 选择一个储存大一点的地方存储找一个空间大的磁盘设备挂载到此目录

[root@localhost ~]# mkdir /opt/data
[root@localhost ~]# chown -R mysql.mysql /opt/data/
[root@localhost ~]# ll /opt/
total 0
drwxr-xr-x. 2 mysql mysql 6 May  6 11:13 data

初始化数据库

[root@localhost ~]# mysqld --initialize --user=mysql --datadir=/opt/data/
2021-05-06T03:16:40.204343Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-06T03:16:40.544276Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-05-06T03:16:40.583123Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-05-06T03:16:40.594012Z 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: 79cebdcb-ae19-11eb-ac91-000c29a69e07.
2021-05-06T03:16:40.595677Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-05-06T03:16:41.199204Z 0 [Warning] CA certificate ca.pem is self signed.
2021-05-06T03:16:41.336981Z 1 [Note] A temporary password is generated for root@localhost: ca2JkmmVua>d		//这里是root用户的临时密码，此处密码是ca2JkmmVua>d
//注意，这个密码是随机的，你的不会跟我一样，一定要记住这个密码，因为一会登录时会用到
[root@localhost ~]# ls /opt/data/		//初始化好了这里就有数据了
auto.cnf         ibdata1             public_key.pem
ca-key.pem       ib_logfile0         server-cert.pem
ca.pem           ib_logfile1         server-key.pem
client-cert.pem  mysql               sys
client-key.pem   performance_schema
ib_buffer_pool   private_key.pem

保留临时密码

[root@localhost ~]# echo 'ca2JkmmVua>d' > pass
[root@localhost ~]# ls
anaconda-ks.cfg  pass

写配置文件

[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql		//指定mysql的安装目录
datadir = /opt/data				//指定数据的存放目录
socket = /tmp/mysql.sock		//指定套接字存放目录
port = 3306						//指定端口号
pid-file = /opt/data/mysql.pid	//指定进程文件存放位置
user = mysql					//指定MySQL以什么用户的身份提供服务
skip-name-resolve				//跳过域名解析，即直接在内网使用ip连接数据库

配置服务启动脚本

root@localhost ~]# cp -a /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld
[root@localhost ~]# ll /etc/init.d/
total 36
-rw-r--r--. 1 root  root  18434 Feb 15 23:09 functions
-rwxr-xr-x. 1 mysql mysql 10576 Dec 10 11:23 mysqld
-rw-r--r--. 1 root  root   1161 Mar 17 03:42 README
[root@localhost ~]# vim /etc/init.d/mysqld
......
basedir=/usr/local/mysql		//环境变量目录
datadir=/opt/data				//数据存储目录
......

启动MySQL

[root@localhost ~]# service mysqld start
Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'.
 SUCCESS! 
[root@localhost ~]# ss -antl
State  Recv-Q Send-Q Local Address:Port Peer Address:Port Process 
LISTEN 0      128          0.0.0.0:22        0.0.0.0:*            
LISTEN 0      80                 *:3306            *:*            
LISTEN 0      128             [::]:22           [::]:*

设置开机启动

[root@localhost ~]# chkconfig mysqld on
[root@localhost ~]# 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

修改密码

[root@localhost ~]# dnf -y install ncurses-compat-libs		//安装这个软件不然使用下面这条命令会报错
[root@localhost ~]# cat pass
ca2JkmmVua>d
[root@localhost ~]# mysql -uroot -p'ca2JkmmVua>d'
mysql> set password = password('填入你要修改的密码');
Query OK, 0 rows affected, 1 warning (0.00 sec)

头文件和库文件配置

[root@localhost ~]# ln -s /usr/local/mysql/include /usr/include/mysql
[root@localhost ~]# vim /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib
[root@localhost ~]# ldconfig

mysql配置文件

mysql的配置文件为/etc/my.cnf

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

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

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

mysql数据库备份与恢复

1 数据库常用备份方案

数据库备份方案：

• 全量备份
• 增量备份
• 差异备份

备份方案	特点
全量备份	全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。 数据恢复快。 备份时间长
增量备份	增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份 与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象 是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量 备份后所产生的增加和修改的文件，如此类推。 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行
差异备份	备份上一次的完全备份后发生变化的所有文件。 差异备份是指在一次全备份后到进行差异备份的这段时间内 对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。

2 mysql备份工具mysqldump

//语法：
    mysqldump [OPTIONS] database [tables ...]
    mysqldump [OPTIONS] --all-databases [OPTIONS]
    mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
    
//常用的OPTIONS：
    -uUSERNAME      //指定数据库用户名
    -hHOST          //指定服务器主机，请使用ip地址
    -pPASSWORD      //指定数据库用户的密码
    -P#             //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307

备份所有数据库

[root@localhost ~]# mysqldump -uroot -p  --all-databases > /mysql_dump/all-20210506.sql
Enter password: 
[root@localhost ~]# ls /mysql_dump/
all-20210506.sql 

备份某个数据库

[root@localhost ~]# mysqldump -uroot -p  --databases hyh > /mysql_dump/hyh_db_20210504.sql
Enter password: 
[root@localhost ~]# ls /mysql_dump/
all-20210506.sql   hyh_db_20210506.sql

备份某个数据库

[root@localhost ~]# mysqldump -uroot -p hyh student > /mysql_dump/student_table_20210506.sql
Enter password: 
[root@localhost ~]# ls /mysql_dump/
all-20210506.sql   hyh_db_20210506.sql  student_table_20210506.sql

mysql数据恢复

恢复数据表

mysql> use hyh
mysql> drop table student;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

[root@localhost ~]# cd /mysql_dump/
[root@localhost mysql_dump]# mysql -uroot -phyh123!.
mysql> use hyh;
Database changed
mysql> source student_table_20210506.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_hanao |
+-----------------+
| student         |
+-----------------+
1 row in set (0.00 sec)
————————————————

恢复数据库

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

[root@localhost mysql_dump]# mysql -uroot -phyh123!. < hyh_db_20210506.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.


[root@localhost mysql_dump]# mysql -uroot -phyh123!. -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hyh                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

恢复所有数据库

mysql> drop database hyh;
Query OK, 1 row affected (0.01 sec)

[root@localhost mysql_dump]# mysql -uroot -phyh123!. < all_20210506.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql_dump]# mysql -uroot -phyh123!. -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hyh                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+