mysql

在centos中配置mysql中的root用户允许远程连接

检查windows与centos之间的网络是否可以联通
在centos中打开终端命令行，依次输入下面命令：

[root@aubin ~]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 5.6.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> 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> select host,user from user;
+-----------+----------+
| host      | user     |
+-----------+----------+
| 127.0.0.1 | root     |
| ::1       | root     |
| aubin.com |          |
| aubin.com | root     |
| localhost |          |
| localhost | chandao  |
| localhost | iwebshop |
| localhost | root     |
+-----------+----------+
8 rows in set (0.00 sec)

mysql> update user set host='%' where host='::1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

设置host的权限为%，把在所有数据库的所有表的所有权限赋值给位于所有IP地址的root用户
centos关闭防火墙，保持mysql开启状态，新建连接连接成.

数据库备份与恢复

[root@aubin data]# mysqldump -u root iwebshop>iwebshop.sql
mysql> create database iwebshop_back charset='utf8';
[root@aubin data]# mysql -u root iwebshop_back<iwebshop.sql

MySQL操作日志

general-log：能记录mysql所有的操作日志（包括增删改查），不过会耗费数据库5%-10%的性能，所以一般没特别需要不要打开此功能，一般在查找问题时才打开，完成后及时关闭。
1.客户端连接mysql
2.查询日志是否开启：show variables like ‘general%’;
3.打开日志：set global general_log=1;
4.不需要使用日志时关闭日志：set global general_log=0;
5.打开对应目录下的日志文件，操作mysql后，观察文件变化，centos中查看文件自动更新：tail -f 文件名