MySQL 操作与备份

2018-12-21 本文已影响0人 fangfc

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 操作与备份

1. 启动MySQL 并修改密码

2. MySQL 常用命令

3. 用户权限管理

3.1 创建用户

3.2 设置用户授权

3.2 删除用户

4. 数据库备份

猜你喜欢

热点阅读