mysql的二进制安装和数据备份

关闭防火墙和selinux

[root@localhost ~]# systemctl stop firewalld.service 
[root@localhost ~]# setenforce 0
[root@localhost ~]# getenforce 
Permissive

创建用户和组，修改目录/usr/local/mysql的属组和属组

[root@localhost src]# groupadd -r mysql
[root@localhost src]# useradd -M -s /sbin/nologin -g mysql mysql 

下载二进制格式的mysql软件包

oot@localhost ~]# cd /usr/src/
[root@localhost src]# wget https://downloads.mysql.com/archives/get/file/my
sql-5.7.22-linux-glibc2.12-x86_64.tar.gz

将软件包解压至/usr/local

[root@localhost ~]# cd /usr/src/
[root@localhost src]# ls
debug  kernels  mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz
[root@localhost src]# tar xf mysql-5.7.23-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.23-linux-glibc2.12-x86_64  share
[root@localhost local]# ln -sv mysql-5.7.23-linux-glibc2.12-x86_64/ mysql 
[root@localhost local]# chown -R mysql.mysql /usr/local/mysql
[root@localhost local]# ll /usr/local/mysql -d
lrwxrwxrwx 1 mysql mysql 36 8月  28 20:24 /usr/local/mysql -> mysql-5.7.23-linux-glibc2.12-x86_64/

添加环境变量

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

建立数据存放目录

[root@localhost local]# mkdir /opt/data
[root@localhost local]# chown -R mysql.mysql /opt/data
[root@localhost local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
2018-08-28T12:28:52.406772Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-08-28T12:28:52.754503Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-08-28T12:28:52.846889Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-08-28T12:28:52.871823Z 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: ec8894c9-aabd-11e8-8c8a-000c2913cc69.
2018-08-28T12:28:52.872821Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-08-28T12:28:52.876757Z 1 [Note] A temporary password is generated for root@localhost: ;1<Y>%U:e,Gp   # 临时密码

生成配置文件

[root@localhost local]# cat > /etc/my.cnf << EOF
[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
EOF

配置服务启动脚本

[root@localhost local]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost local]# vim /etc/init.d/mysqld 
44 # overwritten by settings in the MySQL configuration files.
 45 
 46 basedir=/usr/local/mysqld   #指定MySQL安装路径
 47 datadir=/opt/data   #指定数据库存放路径
 48 
 49 # Default value, in seconds, afterwhich the script should timeout wait    ing

启动mysql

[root@localhost local]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/opt/data/localhost.err'.
 SUCCESS! 
[root@localhost local]# ss -antl
State      Recv-Q Send-Q     Local Address:Port       Peer Address:Port 
LISTEN     0      100            127.0.0.1:25                    *:*     
LISTEN     0      128                    *:22                    *:*     
LISTEN     0      100                  ::1:25                   :::*     
LISTEN     0      80                    :::3306                 :::*       #3306端口号开启
LISTEN     0      32                    :::21                   :::*     
LISTEN     0      128                   :::22                   :::*  

使用临时密码;1%U:e,Gp登录MySQL

[root@localhost local]# 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.23

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)

mysql> \q        #退出mysql
Bye
[root@localhost local]# mysql -uroot -p            #使用密码123456登录
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.23 MySQL Community Server (GPL)

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> 

数据库的备份
创建数据库dxk,在库里面建haha表

mysql> create database dxk;          #创建数据库dxk;
Query OK, 1 row affected (0.00 sec)

mysql> use dxk;
Database changed
mysql> create table haha (id int not null,name char not null,age int not nu
Query OK, 0 rows affected (0.01 sec)           #在dxk数据库中创建haha表

mysql> desc haha;                                         #查看表结构
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
| name  | char(1) | NO   |     | NULL    |       |
| age   | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
mysql> select * from haha;                       #查看haha表的内容
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | p    |   3 |
|  1 | 8    |   3 |
|  1 | -    |   3 |
|  1 | ?    |   3 |
+----+------+-----+
4 rows in set (0.00 sec)

备份数据库

[root@localhost ~]# mysqldump -uroot -p -h192.168.118.128 --all-databases > all-2018.09.28.sql                   #在备份端上将数据库备份到all-2018.09.28.sql文件中
Enter password: 
[root@localhost ~]# mysql -uroot -p -h192.168.118.128 -e 'show databases;'
Enter password:                        #查看备份过来的数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dxk                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

删除dxk数据库，进行数据恢复

mysql> drop database dxk;                        #模拟误删dxk数据库
Query OK, 1 row affected (0.09 sec)

[root@localhost ~]# mysql -uroot -p123456 -h192.168.118.128 <all-2018.09.28.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p123456 -h192.168.118.128 -e 'show databases';
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+                              #备份数据库
| Database           |
+--------------------+
| information_schema |
| dxk                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

服务端查询数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dxk                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> show tables from dxk;
+---------------+
| Tables_in_dxk |
+---------------+
| haha          |
+---------------+
1 row in set (0.00 sec)