mysql cbo_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)

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值