Lamp build 23:mysql Common operation-2

Authorization: All permissions, can also grant partial permissions,% wildcard, representing all

Mysql> Grant all on discuz.* to ' rachy ' @ ' percent ' identified by ' 123456 ';

Query OK, 0 rows affected (0.01 sec)

Refresh permissions to make it effective

mysql> flush Privileges;

Query OK, 0 rows Affected (0.00 sec)

View Task Queue

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)

View variables, which can be defined in/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)

Setting variable values in the MySQL environment can take effect immediately without restarting MySQL, but this is temporary, permanently editing the configuration file

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)

Set Max_connect_errors to 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)

View status

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)

View specified status

Mysql> Show status like '%running ';

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

| variable_name | Value |

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

| slave_running | OFF |

| threads_running | 1 |

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

2 rows in Set (0.00 sec)

Wildcard characters can be added before and after a

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 |

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

Rows in Set (0.00 sec)

configuration files, defining paths

[Email protected] ~]# Vim/etc/init.d/mysqld

......

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

# Overwritten by settings in the MySQL configuration files.

Basedir=/usr/local/mysql

Datadir=/data/mysql

......

MySQL error log Hostname.err

[Email protected] ~]# cd/data/mysql/

[[email protected] mysql]# ls

centos6.5-1.err discuz ib_logfile0 MySQL

Centos6.5-1.pid ibdata1 ib_logfile1 Test

Viewing the error log

[Email protected] 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'll 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)

Fix a table

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 build 23:mysql Common operation-2