Mariadb/Mysql命令行账户权限管理 开启数据库远程访问 批量数据导入导出 备份与恢复

• 用户账户和权限
  • 1 用户账号的基础知识
    • CREATE USER
    • GRANT
    • DROP
    • RENAME
    • 初始化更改root的密码
    • 开启远程访问
      • 方法1
      • 方法2
      • centos7为端口开启防火墙穿透
  • 2. 数据库的备份与恢复
    • 2.1 备份
      • mysqldump
    • 2.2 恢复备份
      • mysql
  • 3. 批量导入数据
    • LOAD DATA INFILE
    • SELECT + INTO OUTFILE

 

用户账户和权限

1 用户账号的基础知识

CREATE USER

CREATE USER 'xiaoming';
#创建一个名为xiaoming的账户，未指定密码和主机

CREATE USER 'lena_stankoska'@'localhost'
IDENTIFIED BY 'her_password_123';
#创建账户指明密码和主机

SHOW GRANTS FOR xiaoming;
#显示xiaoming用户所拥有的权限
+--------------------------------------------+
| Grants for xiaoming@% |
+--------------------------------------------+
| GRANT USAGE ON *.* TO 'xiaoming'@'%' |
+--------------------------------------------+

用户名是 xiaoming，主机是通配符%。之所以用通配符，是因为我们在CREATE USER 时没有指定主机。于是，从任何主机登录这个账号都会拥有通用的权限。 但结果中的*.*是指授权使用所有数据库和所有表（句点前的部分是数据库，句点后的是表）

GRANT

GRANT 权限 ON 数据库.表 
TO '用户'@'主机';

GRANT USAGE ON *.* TO 'lena_stankoska'@'lena_stankoska_home'
IDENTIFIED BY 'her_password_123';
#GRANT也可以用来创建用户，如果用户不存在，此条语句赋予了USAGE权限(无权限)

GRANT ALL ON rookery.*
TO 'lena_stankoska'@'localhost';
#赋予从localhost登录的lena_stankoskarookery用户操作数据库rookery下所有表的权限

SHOW GRANTS FOR 'lena_stankoska'@'localhost';
+---------------------------------------------------------------------+
| Grants for lena_stankoska@localhost |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lena_stankoska'@'localhost' |
| GRANT ALL PRIVILEGES ON `rookery`.* TO 'lena_stankoska'@'localhost' |
+---------------------------------------------------------------------+

GRANT用于给用户赋予权限或者修改权限。

DROP

DROP '用户'@'主机'
#删除用户
DROP 'user'@'localhost'

删除之前可以查询mysql.user账户中存在几个账户，主机名和用户名两个变量定义了一个账户。

RENAME

RENAME '用户名'@'主机名' TO '用户名'@'主机名'
#重命名账户

初始化更改root的密码

登录数据库后使用update语句修改密码
update user set Password=PASSWORD('密码') WHERE User='root';

修改完毕后记得刷新一下数据库权限，或者重启数据库。
mysqladmin -u root -p flush-privileges ;
//这里输入密码还是先使用原来的密码，执行完毕后原来的密码就失效了。
或者直接在数据库中输入
flush privileges;

开启远程访问

方法1

#登陆mysql
$ mysql -uroot -p
mysql> use mysql;
mysql> update user set host = '%' where user = 'root';
mysql> select host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | debian-sys-maint |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
4 rows in set (0.00 sec)
#ok 退出MySQL 重启服务
mysql> quit;
$ service mysql restart
#发现客户端远程还是连接不上 继续修改mysql.cnf配置文件
#需要root权限，配置文件是只读的
$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
#往下翻，注释掉这一行,保存退出
# bind-address          = 127.0.0.1
#重启服务
$ service mysql restart;
#ok 可以了

方法2

#允许所有用户远程访问 修改用户名和密码为你自己的
mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
#允许单个ip 修改用户名和密码为你自己的
mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'1.2.3.4' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
#最后
mysql> FLUSH PRIVILEGES;

centos7为端口开启防火墙穿透

firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

或直接关闭防火墙

systemctl disable firewalld

2. 数据库的备份与恢复

2.1 备份

mysqldump

操作原理：首先查询每个数据库和每个表的结构与数据，然后把查出的所有内容导出到文本文件中。它创建的默认文本文件被称为 dump文件，里面包含重建数据库和数据必需的 SQL 句。如果打开这个文件，你会看到一些 CREATE TABLE 语句，以及大量的INSERT语句。这看似累赘，但实际上很简单，且便于管理。

mysqldump --user=admin_backup \
--password --lock-all-tables
--all-databases > /data/backups/all-dbs.sql
#备份所有的数据库

mysqldump --user=admin_backup --password --lock-tables \
--verbose --databases rookery > rookery.sql
#备份指定的数据库

mysqldump --user=admin_backup --password --lock-tables \
--databases rookery birdwatchers > rookery-birdwatchers.sql
#备份多个数据库，用空格分隔

mysqldump --user=admin_backup --password --lock-tables \
--databases birdwatchers --tables humans > birdwatchers-humans.sql
#备份单个表

#还可省略--databases,以下为另一种写法
mysqldump --user=admin_backup --password --lock-tables \
birdwatchers humans > birdwatchers-humans.sql

自动备份脚本

#!/bin/sh
my_user='admin_back'
my_pwd='my_silly_password'
db1='rookery'
db2='birdwatchers'
date_today=$(date +%Y-%m-%d)
backup_dir='/data/backup/'
dump_file=$db1-$db2-$date_today'.sql'
/usr/bin/mysqldump --user=$my_usr --password=$my_pwd --lock-tables \
--databases $db1 $db2 > $backup_dir$dump_file
exit

2.2 恢复备份

mysql

mysql --user=admin_restore --password < rookery.sql
#恢复数据库备份

mysql --user=admin_restore --password --database=rookery < table.sql
#原来的dump文件只是备份了表，那样就需要在恢复的时候指定恢复到哪一个数据库当中

mysql -uroot --database=xopens --default-character-set=GBK < 脚本-奥特莱斯.txt
#如果中文出现错误的时候只能加上字符集设置，但是具体要会用哪个字符集我就不知道了，现阶段还得自己试试吧

恢复单表的方法

• 修改dump文件，去掉其他表部分，或者取出相关表的部分，然后单独形成一个dump文件，恢复这个dump文件即可。
• 修改dump文件，修改数据库的名字，恢复这个dump文件，然后删除掉原数据库的对应表，再将新产生的这个表，复制或者移动到原数据库中。

3. 批量导入数据

LOAD DATA INFILE

LOAD DATA INFILE '/tmp/Clements-Checklist-6.9-final.csv'
INTO TABLE rookery.clements_list_import
FIELDS TERMINATED BY ',';
#从文件中到入到表中，数据使用','分隔

LOAD DATA INFILE '/tmp/Clements-Checklist-6.9-final.csv'
INTO TABLE rookery.clements_list_import
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
IGNORE 1 LINES;
#ENCLOSED BY 嵌套子句，来指定域用的是双引号。另外，因为并非所有域都这样，所以我们还要在这个嵌套子句前加上 OPTIONALLY 选项。这样就使得 MySQL 在遇到一个双
引号时，会往后寻找另一个，并在找到时，把这一对双引号之间的内容都当成数据（即使其中含有逗号）。

 LOAD DATA LOCAL INFILE '/root/test/single_task.csv'
 INTO TABLE L30MStorage.TaskInfo 
 FIELDS TERMINATED BY ',' 
 ENCLOSED BY '\'' IGNORE 1 LINES;
 #可以加载本地文件到数据库中

SELECT + INTO OUTFILE

SELECT birds.scientific_name,
IFNULL(common_name, ''),
bird_families.scientific_name
FROM rookery.birds
JOIN rookery.bird_families USING(family_id)
JOIN rookery.bird_orders USING(order_id)
WHERE bird_orders.scientific_name = 'Charadriiformes'
ORDER BY common_name
INTO OUTFILE '/tmp/birds-list.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY '|' ESCAPED BY '\\'
LINES TERMINATED BY '\n');
#批量导出数据