LAMP搭建23：MySQL常用操作-2

mysql> grant all on discuz.* to ‘rachy‘@‘%‘ identified by ‘123456‘;

Query OK, 0 rows affected (0.01 sec)

刷新权限，使之生效

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

查看任务队列

mysql> show processlist;

+----+------+-----------+------+---------+------+-------+------------------+

| Id | User | Host      | db   | Command | Time | State | Info             |

+----+------+-----------+------+---------+------+-------+------------------+

| 10 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |

+----+------+-----------+------+---------+------+-------+------------------+

1 row in set (0.01 sec)

查看变量，这些参数都可以在/etc/my.conf中定义

mysql> show variables;

+----------------------------------+---------------------------------------------------------------+

| Variable_name                           | Valu                                                                                      |

+----------------------------------+---------------------------------------------------------------+

| auto_increment_increment        | 1                                                                                           |

……

| wait_timeout                              | 28800                                                                                   |

| warning_count                           | 0                                                                                           |

+----------------------------------+---------------------------------------------------------------+

274 rows in set (0.00 sec)

在mysql环境里设置变量值，可以立即生效，不用重启mysql，但这是临时的，永久还要编辑配置文件

mysql> set global max_connections=200;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like ‘max_connections‘;

+-----------------+-------+

| Variable_name   | Value |

+-----------------+-------+

| max_connections | 200   |

+-----------------+-------+

1 row in set (0.00 sec)

将max_connect_errors设置为100

mysql> set global max_connect_errors=100;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like ‘max_connect%‘;

+--------------------+-------+

| Variable_name      | Value |

+--------------------+-------+

| max_connect_errors | 100   |

| max_connections    | 200   |

+--------------------+-------+

2 rows in set (0.00 sec)

查看状态

mysql> show status;

+------------------------------+---------+

| Variable_name                     | Value     |

+------------------------------+---------+

| Aborted_clients                    | 0            |

| Aborted_connects                | 2            |

……

| Threads_running                   | 1            |

| Uptime                                  | 3310      |

| Uptime_since_flush_status    | 3310      |

+-------------------------------+---------+

291 rows in set (0.00 sec)

查看指定状态

mysql> show status like ‘%running‘;

+-----------------+-------+

| Variable_name   | Value |

+-----------------+-------+

| Slave_running   | OFF   |

| Threads_running | 1     |

+-----------------+-------+

2 rows in set (0.00 sec)

前后都可以加上通配符

mysql> show status like ‘%buffer%‘;

+-----------------------------------+-------+

| Variable_name                     | Value |

+-----------------------------------+-------+

| Innodb_buffer_pool_pages_data     | 19    |

| Innodb_buffer_pool_pages_dirty    | 0     |

| Innodb_buffer_pool_pages_flushed  | 1     |

| Innodb_buffer_pool_pages_free     | 493   |

| Innodb_buffer_pool_pages_misc     | 0     |

| Innodb_buffer_pool_pages_total    | 512   |

| Innodb_buffer_pool_read_ahead_rnd | 1     |

| Innodb_buffer_pool_read_ahead_seq | 0     |

| Innodb_buffer_pool_read_requests  | 84    |

| Innodb_buffer_pool_reads          | 12    |

| Innodb_buffer_pool_wait_free      | 0     |

| Innodb_buffer_pool_write_requests | 1     |

+-----------------------------------+-------+

12 rows in set (0.00 sec)

配置文件，定义路径

[root@centos6 ~]# vim /etc/init.d/mysqld

……

# If you change base dir, you must also change datadir. These may get

# overwritten by settings in the MySQL configuration files.

basedir=/usr/local/mysql

datadir=/data/mysql

……

MySQL错误日志hostname.err

[root@centos6 ~]# cd /data/mysql/

[root@centos6 mysql]# ls

centos6.5-1.err  discuz   ib_logfile0  mysql

centos6.5-1.pid  ibdata1  ib_logfile1  test

查看错误日志

[root@centos6 mysql]# tail centos6.5-1.err

170115 23:54:32 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

170115 23:54:32 mysqld_safe mysqld from pid file /data/mysql/centos6.5-1.pid ended

170115 23:54:33 mysqld_safe Starting mysqld daemon with databases from /data/mysql

170115 23:54:33 [Warning] ‘--skip-locking‘ is deprecated and will be removed in a future release. Please use ‘--skip-external-locking‘ instead.

170115 23:54:33 [Note] Plugin ‘FEDERATED‘ is disabled.

170115 23:54:34  InnoDB: Started; log sequence number 0 44233

170115 23:54:34 [Note] Event Scheduler: Loaded 0 events

170115 23:54:34 [Note] /usr/local/mysql/bin/mysqld: ready for connections.

Version: ‘5.1.49‘  socket: ‘/tmp/mysql.sock‘  port: 3306  MySQL Community Server (GPL)

修复一个表

mysql> use discuz;

Database changed

mysql> repair table discuz.pre_forum_post;

+-----------------------+--------+----------+----------+

| Table                 | Op     | Msg_type | Msg_text |

+-----------------------+--------+----------+----------+

| discuz.pre_forum_post | repair | status   | OK       |

+-----------------------+--------+----------+----------+

1 row in set (0.05 sec)

LAMP搭建23：MySQL常用操作-2

标签：lamp   mysql   常用操作