LAMP搭建23：MySQL常用操作-2
授权：all全部权限，也可以授予部分权限，%通配符，表示所有mysql> grant all on discuz.* to 'rachy'@'%'> Query OK, 0 rows affected (0.01 sec)
刷新权限，使之生效
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
查看任务队列
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
|> +----+------+-----------+------+---------+------+-------+------------------+
| 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)
配置文件，定义路径
# 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
# cd /data/mysql/
# ls
centos6.5-1.errdiscuz ib_logfile0mysql
centos6.5-1.pidibdata1ib_logfile1test
查看错误日志
# tail centos6.5-1.err
170115 23:54:32 /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 '--skip-locking' is deprecated and will be removed in a future> 170115 23:54:33 Plugin 'FEDERATED' is disabled.
170115 23:54:34InnoDB: Started; log sequence number 0 44233
170115 23:54:34 Event Scheduler: Loaded 0 events
170115 23:54:34 /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.1.49'socket: '/tmp/mysql.sock'port: 3306MySQL 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)
页:
[1]