Linux基础(day55)

13.4 mysql用户管理

mysql用户管理目录概要

  • grant all on . to 'user1' identified by 'passwd';
  • grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd';
  • grant all on db1.* to 'user3'@'%' identified by 'passwd';
  • show grants;
  • show grants for user2@192.168.133.1;

mysql用户管理

  • 场景,为了安全,新建的站点,创建新的用户,或者给予使用已有账户,给予权限
  • grant all on . to 'user1' identified by 'passwd';
    • grant 表示 授权
    • all 表示所有权限,查看,创建,删除等等
    • on . to 'user1' identified by 'passwd';
  • 若是登录到mysql中后,输错了字符,并按了回车键,直接输入分号 ; 就会推出, 回到mysql的命令行
  • 退出mysql除了使用 quit 命令,还可以使用 exit 命令,还可以ctrl+d快捷键退出
  1. 登录到mysql
[root@hf-01 ~]# mysql -uroot -p'hanfeng'
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> 
  1. 创建普通用户user1,命令
  • 命令grant all on . to 'user1'@'127.0.0.1' identified by '123456a';——>在输入命令的时候,千万要注意符号,一旦漏失了符号 ' ',那么后面就无法登录到user1的mysql
    • 'user1'@'127.0.0.1' 指定用户@指定来源IP (指定用户可以写 % 就是通配,表示所有的IP)如果指定了来源IP,那么只能通过来源IP登录
    • 符号*.* 表示所有库,所有表
      • 第一个 * 表示库名,可以写成mysql.* 那就表示对mysql所有的表
    • identified by 'passwd' 指定user1的mysql密码
  • grant语句,是不会记录到命令历史中的因为不安全
mysql>  grant all on *.* to 'user1'@'127.0.0.1' identified by '123456a';
Query OK, 0 rows affected (0.02 sec)

mysql> 
  1. 退出数据库,并尝试user1是否可以登录
[root@hf-01 ~]# mysql -uuser1 -p'123456a'
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)
[root@hf-01 ~]# 
  1. 会看到登录失败,因为它默认的是sock,需要指定 -h 指定IP,会看到成功登录到user1的数据库
[root@hf-01 ~]# mysql -uuser1 -p123456a -h127.0.0.1
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> quit
Bye
  1. 授权localhost,授权本地,用sock去连接
  2. 重新登录root,并输入localhost,创建成功后,并退出
  • grant all on . to 'user1'@'localhost' identified by '123456a';
[root@hf-01 ~]#  mysql -uroot -p'hanfeng'
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> grant all on *.* to 'user1'@'localhost' identified by '123456a';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
[root@hf-01 ~]# 
  1. 这时不加-h 也可以登录到user1了,因为现在授权就是针对localhost,localhost就是针对的sock
[root@hf-01 ~]# mysql -uuser1 -p123456a
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> exit
Bye
  1. 退出数据库除了用 quit 命令,还可以用 exit 命令,还可以ctrl+d快捷键退出

针对具体的权限去授权

  • grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd';
    • 针对SELECT,UPDATE,INSERT,针对 db1这个库所有的表给用户user2来源IP,并设定密码
  • grant all on db1.* to 'user3'@'%' identified by 'passwd';
    • 针对所有的IP去授权
  • show grants; 查看所有的授权
    • 在登录到某一用户下,show grants;会查看到当前用户的权限的
    • 登录user1用户的mysql,去查看授权
[root@hf-01 ~]# mysql -uuser1 -p123456a
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> show grants;
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for user1@localhost                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*B012E8731FF1DF44F3D8B26837708985278C3CED' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
  • show grants for user1@127.0.0.1; 指定用户去查看授权
    • 登录root用户的mysql,然后查看user1用户的mysql的授权
[root@hf-01 ~]#  mysql -uroot -p'hanfeng'
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> show grants for user1@'127.0.0.1';
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for user1@127.0.0.1                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'127.0.0.1' IDENTIFIED BY PASSWORD '*B012E8731FF1DF44F3D8B26837708985278C3CED' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

show grants;需求

  • show grants;看的是root
  1. 创建一个用户user2,并做一个授权
  • grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd';
mysql> grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd';
Query OK, 0 rows affected (0.01 sec)

mysql> 
  1. 查看user2的授权
  • show grants for user2@'192.168.133.1';
mysql> show grants for user2@'192.168.133.1';
+------------------------------------------------------------------------------------------------------------------+
| Grants for user2@192.168.133.1                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'192.168.133.1' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0' |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.133.1'                                               |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> 
  1. 有一种情况会用到它,比如说,给192.168.133.1做了授权了,但发现一个IP不够,还有一个192.168.133.2,也就是说user2用户不仅需要在192.168.133.1上登录,还需要在192.168.133.2上登录,这时候就需要把授权的命令全部在执行一遍
  2. 这时候就可以直接把GRANT USAGE ON . TO 'user2'@'192.168.133.1' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0' 复制一遍,将其中192.168.133.1改为192.168.133.2 并在语句结尾加上分号 ;
mysql> GRANT USAGE ON *.* TO 'user2'@'192.168.133.2' IDENTIFIED BY PASSWOORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0';
Query OK, 0 rows affected (0.00 sec)

mysql> 
  1. 然后再将第二行复制GRANT SELECT, INSERT, UPDATE ON db1.* TO 'user2'@'192.168.133.1' 把IP改为192.168.133.2,并加上分号 ;
mysql> GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.133.2';
Query OK, 0 rows affected (0.01 sec)

mysql> 
  1. 这时候在来查看show grants查看192.168.133.2
mysql> show grants for user2@'192.168.133.2';
+------------------------------------------------------------------------------------------------------------------+
| Grants for user2@192.168.133.2                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'192.168.133.2' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0' |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.133.2'                                               |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> 
  1. show grants;会看到同样的密码,同样的用户,唯一改变的就是IP
  2. 在知道mysql的用户名,但不知道密码,也可以这样去授权

13.5 常用sql语句

常用sql语句目录概要

  • select count(*) from mysql.user;
  • select * from mysql.db;
  • select db from mysql.db;
  • select db,user from mysql.db;
  • select * from mysql.db where host like '192.168.%';
  • insert into db1.t1 values (1, 'abc');
  • update db1.t1 set name='aaa' where id=1;
  • truncate table db1.t1;
  • drop table db1.t1;
  • drop database db1;

常用sql语句

  • 增删改查,就是mysql和其他关系型数据库常用的select语句操作命令

查询语句

  1. 首先登录root下的mysql
[root@hf-01 ~]#  mysql -uroot -p'hanfeng'
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> 
  1. 使用db1库
mysql> use db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 
  1. 查看当前库的所有表show tables;
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

mysql> 
  1. 查看表的行数 select count(*) from mysql.user;
  • 库和表中间有个分割符,就是用点 . 分割
mysql> select count(*) from mysql.user;
+----------+
| count(*) |
+----------+
|       12 |
+----------+
1 row in set (0.01 sec)

mysql> 
  • 就是说user表有12行内容
  1. 查看所有的内容 select * from mysql.db;(这样看起来会很乱) ——>可以在后面加上\G,如select * from mysql.db\G;
  • 这里的 * 表示查看所有内容
mysql> select * from mysql.db/G;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/G' at line 1
mysql> select * from mysql.db\G;
*************************** 1. row ***************************
                 Host: %
                   Db: test
                 User: 
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: Y
         Trigger_priv: Y
*************************** 2. row ***************************
 等等等,只截取了一部分
  1. 查看db库的所有内容 select db from mysql.db; 第一个db是字段
mysql> select db from mysql.db;
+---------+
| db      |
+---------+
| test    |
| test\_% |
| db1     |
| db1     |
+---------+
4 rows in set (0.01 sec)

mysql> 
  1. 查db字段和user字段 select db,user from mysql.db;
mysql> select db,user from mysql.db;
+---------+-------+
| db      | user  |
+---------+-------+
| test    |       |
| test\_% |       |
| db1     | user2 |
| db1     | user2 |
+---------+-------+
4 rows in set (0.00 sec)

mysql> 
  1. 模糊查询 select * from mysql.db where host like '192.168.%';
  • like 就是模糊匹配
mysql> select * from mysql.db where host like '192.168.%'\G;
*************************** 1. row ***************************
                 Host: 192.168.133.1
                   Db: db1
                 User: user2
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
*************************** 2. row ***************************
                 Host: 192.168.133.2
                   Db: db1
                 User: user2
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
2 rows in set (0.00 sec)

ERROR: 
No query specified

mysql> 

插入语句

  1. 查看创建的表
mysql> desc db1.t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(4)   | YES  |     | NULL    |       |
| name  | char(40) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql>
  1. 查看db1.t1表的内容,会发现为空
mysql> select * from db1.t1;
Empty set (0.00 sec)

mysql> 
  1. 插入数据到 insert into db1.t1 values (1, 'abc');
  • 插入1, 'abc'到db1.t1表
mysql> insert into db1.t1 values (1, 'abc');
Query OK, 1 row affected (0.02 sec)

mysql> 
  1. 再来查询db1.t1
mysql> select * from db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | abc  |
+------+------+
1 row in set (0.00 sec)

mysql> 
  1. 这样就成功了插入了一条数据,在插入的时候 name 这个字段应该是是一个字符串,字符串需要加上一个单引号 ' ' ,数字可以不加单引号
mysql> insert into db1.t1 values (1, 234);
Query OK, 1 row affected (0.01 sec)

mysql> select * from db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | abc  |
|    1 | 234  |
+------+------+
2 rows in set (0.00 sec)

mysql> 

5.这里没有做限制,这里id和name都可以是相同的,同一个字段里有相同的数字,相同的值 6. 也可以做一些限制,在插入相同的id的时候,就会冲突

update操作

  • 更改db1.t1表 的字符串为name 的数据 和 字符串为id 的数据
  • update db1.t1 set name='aaa' where id=1;
mysql> update db1.t1 set name='aaa' where id=1;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
|    1 | aaa  |
+------+------+
2 rows in set (0.00 sec)

mysql> 

delete操作

  • 删除db1.t1表 的数据 和 字符串为id 的数据
  • delete from db1.t1 where id=1;
mysql> delete from db1.t1 where id=1;
Query OK, 2 rows affected (0.01 sec)

mysql> select * from db1.t1;
Empty set (0.00 sec)

mysql> 

truncate清空一个表

  • 清空表数据 truncate table db1.t1;
    • 即使表的数据清空了,但表的字段依旧存在的
mysql> insert into db1.t1 values (1, 234);
Query OK, 1 row affected (0.00 sec)

mysql> select * from db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | 234  |
+------+------+
1 row in set (0.00 sec)

mysql> truncate table db1.t1;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from db1.t1;
Empty set (0.00 sec)

mysql> desc db1.t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(4)   | YES  |     | NULL    |       |
| name  | char(40) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> 
  • truncate 只是清空的内容,而drop 会清空表的数据并清除表的框架
  • drop 会把表的框架也丢掉 drop table db1.t1;
mysql> drop table db1.t1;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from db1.t1;    //因为表的架构已经不存在了
ERROR 1146 (42S02): Table 'db1.t1' doesn't exist
mysql> 
  • 丢掉表 drop database db1;

总结

  • 在使用mysql的时候,少用 * 这样的操作,因为若是一个表里面的内容很多,select count(*)这样操作就会很耗时,浪费资源
  • 数据库中常用引擎是myisam和innodb,默认mysql库里面都是使用的myisam引擎
    • 特点:myisam引擎,能自动去统计有多少行
      • 在select count(*)查看表的时候会很快
      • use mysql;
      • show create table user\G;
    • 特点:innodb引擎,不会自动统计行数,每次去查询,每次去统计行数,就会很耗时
      • use db1
      • show create table t1;
    • 所以select count(*)这种操作尽量减少,会耗费太多资源

13.6 mysql数据库备份恢复

mysql数据库备份恢复目录概要

  • 备份库 mysqldump -uroot -p123456 mysql > /tmp/mysql.sql
  • 恢复库 mysql -uroot -p123456 mysql < /tmp/mysql.sql
    • 恢复是,必须保证目录一致
  • 备份表 mysqldump -uroot -p123456 mysql user > /tmp/user.sql
  • 恢复表 mysql -uroot -p123456 mysql < /tmp/user.sql
  • 备份所有库 mysqldump -uroot -p -A >/tmp/123.sql
  • 只备份表结构 mysqldump -uroot -p123456 -d mysql > /tmp/mysql.sql

mysql数据库备份恢复

备份库

  1. 在执行mysqldump -uroot -p123456 mysql的时候会看到很多信息,屏幕上显示的这些就是备份的数据
  2. 备份mysql库文件
  • mysqlbak.sql文件就是mysql的备份库文件
[root@hf-01 ~]#  mysqldump -uroot -p'hanfeng' mysql > /tmp/mysqlbak.sql
Warning: Using a password on the command line interface can be insecure.
[root@hf-01 ~]# 
  1. 我们可以通过mysqlbak.sql来恢复数据库,还可以恢复到另外一个数据库里面去
  2. 创建一个新的库mysql2
[root@hf-01 ~]#  mysql -uroot -p'hanfeng' -e "create database mysql2"
Warning: Using a password on the command line interface can be insecure.
[root@hf-01 ~]# 
  1. 恢复库
  • mysql -uroot -phanfeng mysql < /tmp/mysql.sql
[root@hf-01 ~]#  mysql -uroot -p'hanfeng' mysql < /tmp/mysqlbak.sql
Warning: Using a password on the command line interface can be insecure.
[root@hf-01 ~]# 
  1. 进入到数据库里面,在后面加一个mysql2 就会进入到mysql2数据库里面
  • mysql -uroot -p'hanfeng' mysql2
[root@hf-01 ~]#  mysql -uroot -p'hanfeng' mysql2
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> 
  1. 查看数据库
mysql> select database();
+------------+
| database() |
+------------+
| mysql2     |
+------------+
1 row in set (0.00 sec)

mysql> 

备份表

  • 针对库里面的某一个表去做备份,只需要在 库后面 加上 表名字 即可备份
    • 先库 在表,中间是空格
    • 备份表 mysqldump -uroot -p123456 mysql user > /tmp/user.sql
  • 能看到备份的时候,库存在的话,先把库drop掉,然后创建库,表存在的话,先把表drop掉,然后创建表,然后在一步一步的插入每一行数据
[root@hf-01 ~]# mysqldump -uroot -phanfeng mysql user > /tmp/user.sql
Warning: Using a password on the command line interface can be insecure.

[root@hf-01 ~]# less /tmp/user.sql  查看备份表
  • 恢复表的时候,只需要写库的名字,不需要去写表的名字
    • 恢复表 mysql -uroot -p123456 mysql < /tmp/user.sql
  • 恢复mysql2库里面的表
[root@hf-01 ~]# mysql -uroot -phanfeng mysql2 < /tmp/user.sql
Warning: Using a password on the command line interface can be insecure.
[root@hf-01 ~]#

备份所有的库

  • 备份所有库 mysqldump -uroot -phanfeng -A >/tmp/123.sql
    • -A 表示all所有的意思
[root@hf-01 ~]# mysqldump -uroot -phanfeng -A > /tmp/mysql_all.sql
Warning: Using a password on the command line interface can be insecure.
[root@hf-01 ~]# 

[root@hf-01 ~]# less /tmp/mysql_all.sql  
  • 只备份表结构 mysqldump -uroot -phanfeng -d mysql > /tmp/mysql.sql
    • 不需要表的数据,只需要表的语句
  • 备份mysql2的表结构
[root@hf-01 ~]# mysqldump -uroot -phanfeng -d mysql2 > /tmp/mysql.sql
Warning: Using a password on the command line interface can be insecure.
[root@hf-01 ~]#

[root@hf-01 ~]# less /tmp/mysql.sql

示例

  • 两个机器的库备份,一个库备份到另一台机器上
  • 解决:
    • 首先两台机器能够通信
    • 然后mysqldump -h 远程mysql-ip -uuser-ppassword dbname > /本地backup.sql
    • 这样既可备份

扩展

  1. SQL语句教程
  2. 什么是事务?事务的特性有哪些?
  3. 根据binlog恢复指定时间段的数据
  4. mysql字符集调整
  • 使用xtrabackup备份innodb引擎的数据库 innobackupex 备份 Xtrabackup 增量备份
  1. http://zhangguangzhi.top/2017/08/23/innobackex%E5%B7%A5%E5%85%B7%E5%A4%87%E4%BB%BDmysql%E6%95%B0%E6%8D%AE/#%E4%B8%89%E3%80%81%E5%BC%80%E5%A7%8B%E6%81%A2%E5%A4%8Dmysql 相关视频 链接:http://pan.baidu.com/s/1miFpS9M 密码:86dx 链接:http://pan.baidu.com/s/1o7GXBBW 密码:ue2f

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏逸鹏说道

触发器在渗透中的利用

0x01 什么是触发器: 触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。触发器一般用在check约束更加复杂的约束上面。触发器和普通的存储过程的...

3535
来自专栏菜鸟致敬

【2】My SQL 管理

(备注,请忽略奇奇怪怪的大小写不统一) 启动及关闭 MySQL 服务器 首先,我们需要通过以下命令来检查MySQL服务器是否启动:(centos测试) ps ...

3569
来自专栏Aloys的开发之路

Oracle系统表整理+常用SQL语句收集

-- DBA/ALL/USER/V_$/GV_$/SESSION/INDEX开头的绝大部分都是视图 -- DBA_TABLES意为DBA拥有的或可以访问的所有...

25510
来自专栏菩提树下的杨过

mysql 学习笔记

以前主要使用oracle做数据库,现在换成mysql了,发现不一样的地方还是挺多的,记录一下: 一、centos上的yum install方式安装  完全卸...

2187
来自专栏乐沙弥的世界

MySQL 慢查询日志(Slow Query Log)

    同大多数关系型数据库一样,日志文件是MySQL数据库的重要组成部分。MySQL有几种不同的日志文件,通常包括错误日志文件,二进制日志,通用日志,慢查询日...

1632
来自专栏乐沙弥的世界

MySQL 用户与权限管理

    MySQL权限系统的主要功能是证实连接到一台给定主机的用户,并且赋予该用户在数据库上的相关DML,DQL权限。MySQL存取控制包含2个阶段,一是服务器...

2871
来自专栏Laoqi's Linux运维专列

shell脚本备份mysql数据库

第一种方法: #!/bin/sh # 备份数据库 # Mysql 用户名密码 MYSQL_USER=root MYSQL_PASS=root BACKU...

4404
来自专栏运维小白

13.4 mysql用户管理

mysql用户管理目录概要 grant all on . to 'user1' identified by 'passwd'; grant SELECT,UPD...

2286
来自专栏杨建荣的学习笔记

MySQL中如何得到权限信息

最近在做一次MySQL数据迁移的时候,突然发现自己遗漏了一个地方,那就是权限信息没有导出,如果我们使用mysqldump --all-databases...

3125
来自专栏JavaWeb

MySQL-大批量数据如何快速的数据迁移

2952

扫码关注云+社区

领取腾讯云代金券