Mysql数据库安装&备份恢复详细教程（Centos7中安装Mysql、mysqldump全量备份、binlog增量备份、数据恢复、开启定时任务备份数据库）

Mysql数据库安装&备份恢复详细教程

Author xiuhongChen
Date 2020/9/23
Desc Centos7中安装Mysql、mysqldump全量备份、binlog增量备份、数据恢复、开启定时任务备份数据库等

一、Centos7中安装Mysql

注意：虚拟机版本不同，mysql源不一样。

版本centos7安装步骤：

1、配置YUM源

在MySQL官网中下载YUM源rpm安装包：http://dev.mysql.com/downloads/repo/yum/

#下载mysql源安装包
shell> wget --no-check-certificate http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm

#安装mysql源
shell> yum localinstall mysql57-community-release-el7-8.noarch.rpm

#检查mysql源是否安装成功
shell> yum repolist enabled | grep “mysql.-community.”

#如果存在多个数据源，启用或者禁用最新mysql数据源，此处使用Mysql5.7
yum-config-manager --disable mysql55-community
yum-config-manager --disable mysql56-community
yum-config-manager --enable mysql57-community-dmr

yum repolist enabled | grep mysql

虚拟机版本Centos6.5安装步骤:
https://www.cnblogs.com/lzj0218/p/5724446.html
https://www.cnblogs.com/hyl8218/p/5648064.html

1.配置YUM源
#下载mysql源安装包
wget --no-check-certificate dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm

#安装mysql源
yum install mysql-community-release-el6-5.noarch.rpm

#查看安装好的Mysql依赖
yum repolist all | grep mysql

2、安装MySQL

shell> yum install mysql-community-server

3、配置并启动MySQL服务

shell> vim /etc/my.conf

#Linux上Mysql表名区分大小写，所以需要设置表名不区分大小写，同时设置字符集
[mysqld]
lower_case_table_names = 1
character_set_server=utf8

shell> systemctl start mysqld.service #启动Mysql
shell> systemctl restart mysqld.service #重启Mysql
shell> systemctl stop mysqld.service #关闭Mysql
shell> systemctl status mysqld.service #查看Mysql服务状态
备注：centos6.5中启动mysql命令和centos7不一致，service mysqld start

看到下图说明启动成功：

4、修改root默认密码

mysql安装完成之后，在/var/log/mysqld.log文件中给root生成了一个默认密码。
有三种方式找到root默认密码：

• 方法一、找回初始密码(推荐)
  grep "password" /var/log/mysqld.log
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');

• 方法二、使用操作系统账号和密码，进行修改密码
  1、mysql -uroot -p
  2、输入你的centos的密码
  3、UPDATE mysql.user SET authentication_string = PASSWORD('MyNewPassword') WHERE User = ‘root’ AND Host = ‘localhost’;
  注：如果显示 ERROR 1819 (HY000): Your password does not satisfy the current policy requirements，说明你的密码不符合安全要求，
  4、FLUSH PRIVILEGES;
  5、quit;

• 方法三、强制进行修改密码（本例使用）

[root@VM_0_3_centos local]# systemctl stop mysqld.service
[root@VM_0_3_centos local]# 
systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
[root@VM_0_3_centos local]# systemctl start mysqld.service
[root@VM_0_3_centos local]# systemctl status mysqld.service
[root@VM_0_3_centos local]# mysql -u root -p
mysql> UPDATE mysql.user SET authentication_string = PASSWORD(‘123456’) WHERE User = ‘root’ AND Host = ‘localhost’;
mysql> FLUSH PRIVILEGES;
mysql> quit;

5、设置mysql使其可以被远程访问

[root@VM_0_3_centos local]# mysql -u root -p
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' IDENTIFIED BY 'test' WITH GRANT OPTION;
Q