MySQL 操作与备份

作者: fangfc | 来源:发表于2018-12-21 08:23 被阅读0次

MySQL 的管理维护
MySQL 操作与备份
【MySQL】mysqldump备份与恢复
mysql数据库备份与恢复
MySQL 全量增量备份方案 -3- 全备脚本与每日定时备份——
MySQL 备份
mysql备份与还原
mysql总结(持续更新)
mysqldump数据库恢复
mysql备份与恢复

1. 启动MySQL 并修改密码

启动MySQL

[fangfc@node10009 mysql57]$ ./bin/mysqladmin --defaults-file=/opt/app/mysql57/etc/my.cnf  --basedir=/opt/app/mysql57/ & 
[fangfc@node10009 mysql57]$ ss -tan | grep 3306
LISTEN     0      128    127.0.0.1:3306                     *:*   
[fangfc@node10009 mysql57]$ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.22-log 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 [(none)] >

修改root 密码

## 方法一:
MySQL [(none)] > SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');
Query OK, 0 rows affected, 1 warning (0.00 sec)

MySQL [(none)] > FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

## 方法二:
MySQL [(none)] > ALTER USER 'root'@'localhost' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)] > FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

## 方法三:
MySQL [(none)] > UPDATE mysql.user SET authentication_string=PASSWORD('123') WHERE `User`='root' and `Host`='localhost';
Query OK, 0 rows affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 1

MySQL [(none)] > FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

2. MySQL 常用命令

查看和选择操作

show databases
查看当前MySQL 内的数据库

MySQL [(none)] > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

use 选择选择数据库
show tables
查看数据表

MySQL [(none)] > use mysql 
Database changed
MySQL [mysql] > show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
...

查看表结构

MySQL [mysql] > desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
...

查看创建表时使用的语句

MySQL [mysql] > show create table user\G;
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  ...

数据库和数据表操作

创建数据库
- 创建数据库 CREATE DATABASE ...

MySQL [(none)] > CREATE DATABASE IF NOT EXISTS `testdb` CHARACTER SET = 'utf8';
Query OK, 1 row affected (0.01 sec)

MySQL [(none)] >

创建数据表

3. 用户权限管理

3.1 创建用户

MySQL中对用户的区分不单单是用户名, 而是用户名+用户可登陆范围组成一个用户, 并且MySQL 针对每个用户都有单独的权限配置.
创建用户
- 语句: 'CREATE USER'
- 语法:

CREATE USER <user-name> [IDENTIFIED BY <password>]
    [FORCE PASSWORD CHANGE {ON|OFF}]
-------------
<user-name>:
    创建的用户的用户名
[IDENTIFIED BY <password>]
    指定用户登陆口令(密码)
FORCE PASSWORD CHAGE {OFF|ON}
    设置用户登陆时是否需要修改密码,

示例

MySQL [mysql] > CREATE USER `fangfc`@`localhost`IDENTIFIED BY '123';
Current database: mysql
Query OK, 0 rows affected (0.01 sec)

MySQL [mysql] > FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

...
[fangfc@node10009 mysql57]$ mysql -ufangfc -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.22-log 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 [(none)] >

3.2 设置用户授权

使用CREATE USER 创建用户时, 是没有权限的.

# 使用root 用户查看该用户的权限
MySQL [(none)] > SELECT * FROM mysql.user WHERE User='fangfc'\G
*************************** 1. row ***************************
                  Host: localhost
                  User: fangfc
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
      password_expired: N
 password_last_changed: 2018-12-21 04:03:51
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

可以看到用户的的权限都是 N, 说明该用户没有权限.
设置用户权限
- 命令GREAT
语法

GRANT <priv_type> ON <priv_level> TO <user> [auth_option];
---
priv_type:
    指定权限 
    如: Drop, Insert,ALL...
priv_level:
    指定可操作的数据库/数据表 
    db_name.tab_name  或 *.*
user:
    用户名@主机
auth_option:
    可以设置用户登陆口令. 
    如 : IDENTIFIED BY '123'

示例:

MySQL [(none)] > GRANT select ON *.* TO testuser@localhost IDENTIFIED BY '123';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

MySQL [(none)] > FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

3.2 删除用户

使用 DROP USER

MySQL [(none)] > SELECT COUNT(`user`) FROM `mysql`.`user`;
+---------------+
| COUNT(`user`) |
+---------------+
|             5 |
+---------------+
1 row in set (0.00 sec)

MySQL [(none)] > DROP USER 'testuser'@'localhost';
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)] > SELECT COUNT(`user`) FROM `mysql`.`user`;
+---------------+
| COUNT(`user`) |
+---------------+
|             4 |
+---------------+
1 row in set (0.00 sec)

创建数据表
- CREATE TABLE

MySQL [testdb] > CREATE 
MySQL [testdb] > CREATE TABLE IF NOT EXISTS `test1`( 
    -> `id` int(10) AUTO_INCREMENT PRIMARY KEY,
    -> `name` varchar(30) NOT NULL) Engine=InnoDB DEFAULT CHARACTER SET='utf8';

Query OK, 0 rows affected (0.02 sec)

MySQL [testdb] > SHOW TABLES;
+------------------+
| Tables_in_testdb |
+------------------+
| test1            |
+------------------+
1 row in set (0.00 sec)

删除数据表
- DROP TABLE ...

MySQL [testdb] > DROP TABLE `test1`;
Query OK, 0 rows affected (0.02 sec)

MySQL [testdb] > SHOW TABLES;
Empty set (0.00 sec)

4. 数据库备份

使用Xtrabackup 备份数据库

官网下载 xtrabackup
安装xtrabackup

[root@node10009 src]# yum install  install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm 
Loaded plugins: fastestmirror
....
Installed:
  percona-xtrabackup-24.x86_64 0:2.4.12-1.el7                                                                            
Dependency Installed:
  libev.x86_64 0:4.15-7.el7                                  perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7               
  perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7                perl-DBD-MySQL.x86_64 0:4.023-6.el7                        
  perl-DBI.x86_64 0:1.627-4.el7                              perl-Digest.noarch 0:1.17-245.el7                          
  perl-Digest-MD5.x86_64 0:2.52-3.el7                        perl-IO-Compress.noarch 0:2.061-2.el7                      
  perl-Net-Daemon.noarch 0:0.48-5.el7                        perl-PlRPC.noarch 0:0.2020-14.el7

创建MySQL 备份时使用的用户

MySQL [(none)] > GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, CREATE TABLESPACE, SUPER, PROCESS ON *.* TO 'backup'@'localhost' IDENTIFIED BY '123';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

MySQL [(none)] > SELECT User,Host FROM mysql.user WHERE `user`='backup';
+--------+-----------+
| User   | Host      |
+--------+-----------+
| backup | localhost |
+--------+-----------+
1 row in set (0.00 sec)

. 全量备份数据库

[root@node10009 src]# mkdir /opt/backup
[root@node10009 src]# cd /opt/backup
[root@node10009 backup]# innobackupex --defaults-file=/opt/app/mysql57/etc/my.cnf --user=backup --password=123 /opt/backup/
...
181221 06:53:11 Backup created in directory '/opt/backup/2018-12-21_06-53-09/'
MySQL binlog position: filename 'mysql_bin.000016', position '3840'
181221 06:53:11 [00] Writing /opt/backup/2018-12-21_06-53-09/backup-my.cnf
181221 06:53:11 [00]        ...done
181221 06:53:11 [00] Writing /opt/backup/2018-12-21_06-53-09/xtrabackup_info
181221 06:53:11 [00]        ...done
xtrabackup: Transaction log of lsn (2516426) to (2516435) was copied.
181221 06:53:11 completed OK!

看到 completed 就说明备份成功了.

[root@node10009 backup]# ls
2018-12-21_06-53-09
[root@node10009 backup]# ls 2018-12-21_06-53-09/
backup-my.cnf   ibdata1  performance_schema  test    undo001  undo003  xtrabackup_binlog_info  xtrabackup_info
ib_buffer_pool  mysql    sys                 testdb  undo002  undo004  xtrabackup_checkpoints  xtrabackup_logfile
[root@node10009 backup]#

尝试修改数据, 然后恢复

修改数据

MySQL [(none)] > select `User` from mysql.user;
+---------------+
| User          |
+---------------+
| backup        |
| fangfc        |
| mysql.session |
| mysql.sys     |
| root          |
+---------------+
5 rows in set (0.00 sec)

MySQL [(none)] > DROP USER 'fangfc'@'localhost';
Query OK, 0 rows affected (0.02 sec)

MySQL [(none)] > select `User` from mysql.user;
+---------------+
| User          |
+---------------+
| backup        |
| mysql.session |
| mysql.sys     |
| root          |
+---------------+
4 rows in set (0.00 sec)

恢复数据

####  备份了的数据库机型事务恢复. 使用--apply-log.  
[root@node10009 backup]# innobackupex --apply-log /opt/backup/2018-12-21_06-53-09/
....
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2517051
181221 07:55:03 completed OK!

#### 恢复数据到指定目录
[root@node10009 backup]# innobackupex --datadir=/opt/backup/2018-12-21_06-53-09-full-backup  --copy-back /opt/backup/2018-12-21_06-53-09/
xtrabackup: recognized server arguments: --datadir=/opt/backup/2018-12-21_06-53-09-full-backup 
.....
181221 08:04:30 [01]        ...done
181221 08:04:30 [01] Copying ./ibtmp1 to /opt/backup/2018-12-21_06-53-09-full-backup/ibtmp1
181221 08:04:30 [01]        ...done
181221 08:04:30 completed OK!

将数据恢复到mysql 的data目录下, (先清空或备份 data目录)

[root@node10009 backup]# cd /opt/app/mysql57/data/data/
[root@node10009 data]# ls
auto.cnf  ib_buffer_pool  ibdata1  mysql  performance_schema  sys  test  testdb
[root@node10009 data]# rm ./* -rf
[root@node10009 data]# cp -r /opt/backup/2018-12-21_06-53-09-full-backup/* .
[root@node10009 data]# chown -R mysql:mysql  ./*

重启

[root@node10009 data]# systemctl restart mysqld
[root@node10009 data]# ss -tan | grep 3306
LISTEN     0      128    127.0.0.1:3306                     *:*                  
[root@node10009 data]#

查看数据

[root@node10009 data]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.22-log 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 [(none)] > select `User` from mysql.user;
+---------------+
| User          |
+---------------+
| backup        |
| fangfc        |
| mysql.session |
| mysql.sys     |
| root          |
+---------------+
5 rows in set (0.00 sec)

可以看到 fangfc 用户的数据恢复了.

END

MySQL 的管理维护
简介包含备份、创建用户、给用户授权、创建表等操作。备份与恢复创建用户选择要操作的Mysql数据库 USE ...
MySQL 操作与备份
1. 启动MySQL 并修改密码启动MySQL 修改root 密码 2. MySQL 常用命令查看和选择操作 ...
【MySQL】mysqldump备份与恢复
简介：mysqldump常用于MySQL数据库逻辑备份。备份操作： 1.备份所有库： 2.备份单个库： 3.备份...
mysql数据库备份与恢复
数据库备份使用mysql自带备份命令行就可实现数据库备份与恢复备份全部库 mysql> mysqldump -...
MySQL 全量增量备份方案 -3- 全备脚本与每日定时备份——
MySQL 全备与增备方案请参考 MySQL 全量增量备份方案 -1-全量备份实现 MySQL 全量增量备份方案 ...
MySQL 备份
备份用户需要的完整权限 MySQL逻辑备份 mysqldump 逻辑备份特点备份的是建表、建库、插入等操作所执行...
mysql备份与还原
一、备份常用操作基本命令 1、备份命令mysqldump格式 2、备份MySQL数据库为带删除表的格式备份MyS...
mysql总结(持续更新)
资料官方mysql 5.1文档酷壳mysql文章配置绿色版Mysql的安装配置备份 MySQL的备份与还...
mysqldump数据库恢复
知识点；简单mysql数据库恢复操作；完整备份方式；
mysql备份与恢复
mysql数据库备份与恢复一、为什么要备份二、备份类型三、备份种类四、逻辑备份逻辑备份特点用法日常用...