首页 > 代码库 > 3.4Mysql 的常用操作
3.4Mysql 的常用操作
[root@OBird ~]# mysql -uroot -pzaq12wsx #入库
mysql> grant all on discuz.* to ‘user1‘@‘10.72.4.% identified by ‘password‘;
# 给某一个数据库授权一个网段都可以访问 “%” 表示通配
mysql> grant all on discuz.* to ‘user1‘@‘%‘ identified by ‘password‘; # 此处是密码
#也可以是全部的网络都开放，但是不建议这样做。
———————————————————————————————————————
mysql> grant all on discuz.* to ‘user1‘@‘%‘ identified by ‘password‘;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 3
Current database: *** NONE ***
Query OK, 0 rows affected (0.00 sec)
————————————————————————————————————————
创建完这个用户后，并没有即时生效权限。需要刷新
————————————————————————————
mysql> flush privileges; # 刷新权限命令
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 4
Current database: *** NONE ***
Query OK, 0 rows affected (0.00 sec)
————————————————————————————
--------------------------------------------------------
mysql> show processlist; # 查看数据库当前有哪些对列，知道数据库在做什么
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 5
Current database: *** NONE ***
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 5 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
————————————————————————————————————————————
mysql> show variables; # 查看系统有哪些变量
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 6
Current database: *** NONE ***
+-----------------------------------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value |
+-----------------------------------------+-------------------------------------------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
| back_log | 50
——————————————————————————————————————————
有时重新更改配置文件后需要重启服务才能生效，而此时这个服务正在使用中，那么可以直接在数据库内来进行操作。
如果当前 mysql 正在运行，用户想修改某个参数
以 | max_connections | 151 为例
mysql> set global max_connections=200; # 手动进行修改
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 8
Current database: *** NONE ***
Query OK, 0 rows affected (0.00 sec)
——————————————————
查看
mysql> show variables like ‘max_connec%‘; # “%” 是通配，
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 10 |
| max_connections | 200 | #原来的数值是151 ，现在变成了 200.
+--------------------+-------+
2 rows in set (0.00 sec)
#原来的数值是151 ，现在变成了 200. 重是一重启，这个数值还是会恢复到原来的黙认值。
如果想要永久生效，需要更改 my.cnf 文件。
—————————————————————————————————————————
查看状态
mysql> show status; # 调优时用的比较多，也可以使用通配
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 62
Current database: *** NONE ***
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| Aborted_clients | 6 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 78 |
| Bytes_sent | 71 |
| Com_admin_commands | 0 |
......
mysql> show status like ‘%running‘; # 通配查询，前后都可以加 " %buffer% "
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 63
Current database: *** NONE ***
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Slave_running | OFF |
| Threads_running | 1 |
+-----------------+-------+
2 rows in set (0.00 sec)
--------------------------------------------------
查看mysql 的错误日志
[root@OBird ~]# vim /etc/init.d/mysqld # 楼主找到该件下，并没有找到 datadir=/data/mysql 这一条
[root@OBird ~]# cd /data/mysql/
[root@OBird mysql]# ls
ibdata1 mysql-bin.000001 mysql-bin.000005 mysql-bin.000009 mysql-bin.000013 mysql-bin.000017 slow.log
ib_logfile0 mysql-bin.000002 mysql-bin.000006 mysql-bin.000010 mysql-bin.000014 mysql-bin.index test
ib_logfile1 mysql-bin.000003 mysql-bin.000007 mysql-bin.000011 mysql-bin.000015 OBird.err
mysql mysql-bin.000004 mysql-bin.000008 mysql-bin.000012 mysql-bin.000016 OBird.pid
OBird.err 就是日志文件
[root@OBird mysql]# tail OBird.err #查看日志文件
160923 8:55:52 InnoDB: Initializing buffer pool, size = 8.0M
160923 8:55:52 InnoDB: Completed initialization of buffer pool
160923 8:55:52 InnoDB: Started; log sequence number 0 44233
160923 8:55:52 [Note] Event Scheduler: Loaded 0 events
160923 8:55:52 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: ‘5.1.73-log‘ socket: ‘/tmp/mysql.sock‘ port: 3306 MySQL Community Server (GPL)
160923 12:58:27 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown
160923 12:58:27 [Note] Event Scheduler: Purging the queue. 0 events
160923 12:58:27 InnoDB: Starting shutdown...
--------------------------------------------------------------------
修复 mysql 中的表。
[root@OBird mysql]# mysql -uroot -pzaq12wsx # 进入mysql
mysql> repair table discuz.pre_forum_post; # 修复语句 ，repair table + 表名 + ； ，
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 66
Current database: *** NONE ***
+-----------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------+--------+----------+----------+
| discuz.pre_forum_post | repair | status | OK |
+-----------------------+--------+----------+----------+
1 row in set (0.04 sec)
本文出自 “CBO#Boy_Linux之路” 博客，请务必保留此出处http://20151213start.blog.51cto.com/9472657/1865903
3.4Mysql 的常用操作