MySQL授权 & MySQL备份

MySQL授权 & MySQL备份

六月 22, 2018

记大数据MySQL备份

1
2
3
4
5
6
7
8
9
10
11
12
13
[root@ip data]# cat mysql_back.sh

#!/bin/bash
user=user
pass=pass
table1=table1
table2=table2
date=`date +%Y%m%d --date "-1 days"`

mysqldump -u$user -p$pass --default-character-set=utf8 --comments=FALSE --tables --no-create-info=FALSE --add-drop-table=TRUE --no-data=FALSE $table1 | sed 's/AUTO_INCREMENT=[0-9]*\s//g' >/data/backup_CN/$table1$date.sql
mysqldump -u$user -p$pass --default-character-set=utf8 --comments=FALSE --tables --no-create-info=FALSE --add-drop-table=TRUE --no-data=FALSE $table2 | sed 's/AUTO_INCREMENT=[0-9]*\s//g' >/data/backup_JP/$table2$date.sql
find backup_CN/ -name "*.sql" -type f -mtime +5 -exec rm {} \; > /dev/null 2>&1
find backup_JP/ -name "*.sql" -type f -mtime +5 -exec rm {} \; > /dev/null 2>&1

mysql5.7 改root密码授权

1
2
3
# safe_mysqld --skip-grant-tables --user=root &
mysql> update mysql.user set password=PASSWORD('新密码') where User='root';
mysql> flush privileges;

创建一个用户叫test1 并授权

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
mysql> grant select,insert,update,delete on *.* to test1@”%” Identified by “abc”;
mysql> FLUSH PRIVILEGES;

查看授权表
mysql> select user,host from mysql.user;

取消授权
mysql> revoke all privileges on *.* from test1@"%”;

删除用户
mysql> use mysql
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> delete from user where user - "test1" and host = "public ip";
Query OK, 0 rows affected (0.00 sec)

mysql> delete from user where user - "test1" and host = "private ip";
Query OK, 0 rows affected (0.00 sec)

mysql> delete from user where user - "test1" and host = "localhost";
Query OK, 0 rows affected, 3 warnings (0.00 sec)

mysql> drop user test1@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec

记一次mysql授权,允许远程连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
mysql> grant all privileges on *.*  to 'root'@localhost identified by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> update mysql.user set password=password('123456') where user='root';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 4 Changed: 3 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> \q
Bye

[root@ip ops_user]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]

[root@ip ops_user]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.

mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

#####

远程连接mysql

# /usr/local/mysql/bin/mysql -h【IP】 -uroot -p123456

#####################
实现远程连接(授权法)
mysql> use mysql;
mysql> grant all privileges on *.* to root@'%' identified by "password”;
mysql> flush privileges;
mysql> select host,user,password from user;

实现远程连接(改表法)
mysql> use mysql;
mysql> update user set host = '%' where user = 'root';

mysql备份单独一张表

1
# mysqldump  -uroot -p123456 --default-character-set=utf8 --comments=FALSE --tables --no-create-info=FALSE --add-drop-table=TRUE --no-data=FALSE test | sed 's/AUTO_INCREMENT=[0-9]*\s//g' >test_20180626.sql

备份导入MySQL

1
2
mysql> use test;
mysql> source /home/test/0101_0630_up_subjects.sql