Common operations for 3.4Mysql

[Email protected] ~]# mysql-uroot-pzaq12wsx #入库

Mysql> Grant all on discuz.* to ' user1 ' @ ' 10.72.4.% identified by ' password ';

# Grant a database a network segment can access "%" to indicate a wildcard

Mysql> Grant all on discuz.* to ' user1 ' @ ' percent ' identified by ' password '; # here is the password

#也可以是全部的网络都开放, but it is not recommended.

———————————————————————————————————————

Mysql> Grant all on discuz.* to ' user1 ' @ ' percent ' 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)

————————————————————————————————————————

After the user has been created, there is no immediate permission to take effect. Need to refresh

————————————————————————————

Mysql> flush Privileges; # Refresh Permissions Command

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; # See what columns the database currently has, know what the database is doing

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; # See what variables the system has

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

——————————————————————————————————————————

Sometimes it is necessary to restart the service after changing the configuration file to take effect, while the service is in use and can be manipulated directly within the database.

If the current MySQL is running, the user wants to modify a parameter

to | max_connections | 151 for example

mysql> set global max_connections=200; # Manually make changes

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)

——————————————————

View

Mysql> Show variables like ' max_connec% '; # "%" is a wildcard,

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

| variable_name | Value |

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

| max_connect_errors | 10 |

|  max_connections | #原来的数值是151, now becomes 200.

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

2 rows in Set (0.00 sec)

#原来的数值是151, now becomes 200. Heavy is a restart, this value will revert to the original MO recognition value.

If you want to take effect permanently, you need to change the my.cnf file.

—————————————————————————————————————————

View status

Mysql> Show status; # Use a lot more when tuning, or you can work with a wildcard

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 '; # wildcard query, can add "%buffer%" before and after

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)

--------------------------------------------------

View the MySQL error log

[[email protected] ~]# vim/etc/init.d/mysqld # Landlord found the piece, and did not find datadir=/data/mysql this article

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

[[email protected] 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 is the log file.

[Email protected] 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 ...

--------------------------------------------------------------------

Fix the tables in MySQL.

[[email protected] mysql]# mysql-uroot-pzaq12wsx # Enter MySQL

mysql> Repair table discuz.pre_forum_post; # Fix statement, repair table + name +;

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)

This article is from the "Cbo#boy_linux Road" blog, make sure to keep this source http://20151213start.blog.51cto.com/9472657/1865903

Common operations for 3.4Mysql