一、创建数据库查看:
例如:在网站创建test_db2、test_db1 ,登录mysql命令行查看:
show databases;
二、创建用户没有授权数据库查看: show grants for testjz; (没有授权16条记录)
三、创建用户授权N个数据库查看: show grants for testjz; (授权16+N条记录)
注:创建账号登录后查看只有
information_schema 、
mysql 、
performance_schema三个系统库和授权的数据库
[root@4xvt6 ~]# mysql -utestjz -pJizheng1987
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test_db |
| test_db1 |
+--------------------+
5 rows in set (0.00 sec)
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test_db |
| test_db1 |
+--------------------+
5 rows in set (0.00 sec)
四、账号密码修改
[root@4xvt6 ~]# mysql -utestjz -pJizheng1987
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'testjz'@'localhost' (using password: YES)
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'testjz'@'localhost' (using password: YES)
[root@4xvt6 ~]# mysql -utestjz -pJizheng2017
五、添加白名单
说明:
主备虚机互相添加白名单访问策略: 主备ip分别为
10.0.48.49、
10.0.49.181
用户添加的ip白名单为
124.127.244.1
执行如下命令
iptables -nvL
查看白名单策略
[root@4xvt7 ~]# iptables -nvL
Chain INPUT (policy ACCEPT 82 packets, 8765 bytes)
pkts bytes target prot opt in out source destination
45 8243 ACCEPT all -- eth0 * 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED
0 0 ACCEPT icmp -- eth0 * 0.0.0.0/0 0.0.0.0/0
0 0 ACCEPT all -- lo * 0.0.0.0/0 0.0.0.0/0
6 468 IP_ACCEPT all -- eth0 * 0.0.0.0/0 0.0.0.0/0
0 0 REJECT all -- eth0 * 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
Chain FORWARD (policy ACCEPT 0 packets, 0 bytes)
pkts bytes target prot opt in out source destination
0 0 REJECT all -- eth0 * 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
Chain OUTPUT (policy ACCEPT 54 packets, 9861 bytes)
pkts bytes target prot opt in out source destination
Chain IP_ACCEPT (1 references)
pkts bytes target prot opt in out source destination
0 0 ACCEPT all -- * * 124.127.244.1 0.0.0.0/0
0 0 ACCEPT all -- * * 10.0.48.49 0.0.0.0/0
6 468 DROP all -- * * 0.0.0.0/0 0.0.0.0/0
Chain INPUT (policy ACCEPT 82 packets, 8765 bytes)
pkts bytes target prot opt in out source destination
45 8243 ACCEPT all -- eth0 * 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED
0 0 ACCEPT icmp -- eth0 * 0.0.0.0/0 0.0.0.0/0
0 0 ACCEPT all -- lo * 0.0.0.0/0 0.0.0.0/0
6 468 IP_ACCEPT all -- eth0 * 0.0.0.0/0 0.0.0.0/0
0 0 REJECT all -- eth0 * 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
Chain FORWARD (policy ACCEPT 0 packets, 0 bytes)
pkts bytes target prot opt in out source destination
0 0 REJECT all -- eth0 * 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
Chain OUTPUT (policy ACCEPT 54 packets, 9861 bytes)
pkts bytes target prot opt in out source destination
Chain IP_ACCEPT (1 references)
pkts bytes target prot opt in out source destination
0 0 ACCEPT all -- * * 124.127.244.1 0.0.0.0/0
0 0 ACCEPT all -- * * 10.0.48.49 0.0.0.0/0
6 468 DROP all -- * * 0.0.0.0/0 0.0.0.0/0
[root@4xvt6 ~]# iptables -nvL
Chain INPUT (policy ACCEPT 193 packets, 14644 bytes)
pkts bytes target prot opt in out source destination
45 2893 ACCEPT all -- eth0 * 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED
0 0 ACCEPT all -- eth1 * 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED
0 0 ACCEPT icmp -- eth0 * 0.0.0.0/0 0.0.0.0/0
0 0 ACCEPT icmp -- eth1 * 0.0.0.0/0 0.0.0.0/0
0 0 ACCEPT all -- lo * 0.0.0.0/0 0.0.0.0/0
7 528 IP_ACCEPT all -- eth0 * 0.0.0.0/0 0.0.0.0/0
6 468 IP_ACCEPT all -- eth1 * 0.0.0.0/0 0.0.0.0/0
0 0 REJECT all -- eth0 * 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
0 0 REJECT all -- eth1 * 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
Chain FORWARD (policy ACCEPT 0 packets, 0 bytes)
pkts bytes target prot opt in out source destination
0 0 REJECT all -- eth0 * 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
0 0 REJECT all -- eth1 * 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
Chain OUTPUT (policy ACCEPT 99 packets, 14524 bytes)
pkts bytes target prot opt in out source destination
Chain IP_ACCEPT (2 references)
pkts bytes target prot opt in out source destination
0 0 ACCEPT all -- * * 124.127.244.1 0.0.0.0/0
1 60 ACCEPT all -- * * 10.0.49.181 0.0.0.0/0
12 936 DROP all -- * * 0.0.0.0/0 0.0.0.0/0
Chain INPUT (policy ACCEPT 193 packets, 14644 bytes)
pkts bytes target prot opt in out source destination
45 2893 ACCEPT all -- eth0 * 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED
0 0 ACCEPT all -- eth1 * 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED
0 0 ACCEPT icmp -- eth0 * 0.0.0.0/0 0.0.0.0/0
0 0 ACCEPT icmp -- eth1 * 0.0.0.0/0 0.0.0.0/0
0 0 ACCEPT all -- lo * 0.0.0.0/0 0.0.0.0/0
7 528 IP_ACCEPT all -- eth0 * 0.0.0.0/0 0.0.0.0/0
6 468 IP_ACCEPT all -- eth1 * 0.0.0.0/0 0.0.0.0/0
0 0 REJECT all -- eth0 * 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
0 0 REJECT all -- eth1 * 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
Chain FORWARD (policy ACCEPT 0 packets, 0 bytes)
pkts bytes target prot opt in out source destination
0 0 REJECT all -- eth0 * 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
0 0 REJECT all -- eth1 * 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
Chain OUTPUT (policy ACCEPT 99 packets, 14524 bytes)
pkts bytes target prot opt in out source destination
Chain IP_ACCEPT (2 references)
pkts bytes target prot opt in out source destination
0 0 ACCEPT all -- * * 124.127.244.1 0.0.0.0/0
1 60 ACCEPT all -- * * 10.0.49.181 0.0.0.0/0
12 936 DROP all -- * * 0.0.0.0/0 0.0.0.0/0
六、查看主备虚机
1.查看当前虚机是否是主、从虚机(
read_only为ON为从虚机;OFF为主虚机
)
mysql> show global variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | ON |
+---------------+-------+
1 row in set (0.00 sec)
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show global variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | OFF |
+---------------+-------+
1 row in set (0.00 sec)
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | OFF |
+---------------+-------+
1 row in set (0.00 sec)
2.知道root密码的情况下查看(返回数据为空则为主虚机)
mysql> show slave status;
Empty set (0.00 sec)
Empty set (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.48.49
Master_User: repl
Master_Port: 3306
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.48.49
Master_User: repl
Master_Port: 3306
注:
Master_Host: 为主虚机ip
Master_User为主从同步的账号
Master_Port 主虚机的端口
七、登录pma
创建一台windows的云主机
1.通过内网验证:输入http://内网:8080/phpmyadmin 提示输入用户名和密码
输入成功后如下所示
八、备份与恢复
1.备份的文件存储在存储服务器(10.12.29.53)的目录下
规则: /smart/database/backup/客户id/数据库实例uuid/data/logical_backup
/smart/database/backup/1100003/8299337/data/logical_backup
[root@TStorage03 logical_backup]# pwd
/smart/database/backup/1100003/8299337/data/logical_backup
[root@TStorage03 logical_backup]# ll
total 20
-rw-r--r--. 1 root root 0 Dec 4 20:18 d121111_backup_20171204_201843.log
-rw-r--r--. 1 root root 0 Dec 4 20:27 d121111_recovery_20171204_202720.log
-rw-r--r--. 1 root root 20480 Dec 4 20:48 ins125186514_data_20171204_204803.tar
-rw-r--r--. 1 root root 0 Dec 4 20:18 test_db1_backup_20171204_201843.log
-rw-r--r--. 1 root root 0 Dec 4 20:48 test_db1_backup_20171204_204803.log
-rw-r--r--. 1 root root 0 Dec 4 20:27 test_db1_recovery_20171204_202720.log
-rw-r--r--. 1 root root 0 Dec 4 20:18 test_db2_backup_20171204_201843.log
-rw-r--r--. 1 root root 0 Dec 4 20:48 test_db2_backup_20171204_204803.log
-rw-r--r--. 1 root root 0 Dec 4 20:27 test_db2_recovery_20171204_202720.log
-rw-r--r--. 1 root root 0 Dec 4 20:18 test_db_backup_20171204_201843.log
-rw-r--r--. 1 root root 0 Dec 4 20:48 test_db_backup_20171204_204803.log
-rw-r--r--. 1 root root 0 Dec 4 20:27 test_db_recovery_20171204_202720.log
/smart/database/backup/1100003/8299337/data/logical_backup
[root@TStorage03 logical_backup]# ll
total 20
-rw-r--r--. 1 root root 0 Dec 4 20:18 d121111_backup_20171204_201843.log
-rw-r--r--. 1 root root 0 Dec 4 20:27 d121111_recovery_20171204_202720.log
-rw-r--r--. 1 root root 20480 Dec 4 20:48 ins125186514_data_20171204_204803.tar
-rw-r--r--. 1 root root 0 Dec 4 20:18 test_db1_backup_20171204_201843.log
-rw-r--r--. 1 root root 0 Dec 4 20:48 test_db1_backup_20171204_204803.log
-rw-r--r--. 1 root root 0 Dec 4 20:27 test_db1_recovery_20171204_202720.log
-rw-r--r--. 1 root root 0 Dec 4 20:18 test_db2_backup_20171204_201843.log
-rw-r--r--. 1 root root 0 Dec 4 20:48 test_db2_backup_20171204_204803.log
-rw-r--r--. 1 root root 0 Dec 4 20:27 test_db2_recovery_20171204_202720.log
-rw-r--r--. 1 root root 0 Dec 4 20:18 test_db_backup_20171204_201843.log
-rw-r--r--. 1 root root 0 Dec 4 20:48 test_db_backup_20171204_204803.log
-rw-r--r--. 1 root root 0 Dec 4 20:27 test_db_recovery_20171204_202720.log
2、恢复:将数据库中创建的表中数据删除部分,测试恢复是否正常