在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 文件名