MySQL usage instructions

001 view mysql version -- versionmysqlselectversion (); mysqlstatus; 002 create a MySQL user and authorize insertintomysql. user (Host, User, Password) values (localhost, username, password (yourpassword); GRANTALLPRIVILEGESON *. * TOusername @ localhost

001 check mysql -- version mysql select version (); mysql status; 002 create a MySQL user and authorize insert into mysql. user (Host, User, Password) values (localhost, username, password (yourpassword); grant all privileges on *. * TO 'username' @ 'localhost'

001 view version

Mysql -- version
Mysql> select version ();
Mysql> status;

002 create a MySQL user and authorize

Insert into mysql. user (Host, User, Password) values ("localhost", "username", password ("yourpassword "));

Grant all privileges on *. * TO 'username' @ 'localhost' identified by "yourpassword" with grant option;

Flush privileges;

003. query supported storage engines
Show engines;

004 view Variables
Show variables like 'innodb % ';

005 import database (SQL file)
Mysql-uroot-p dbname <dbname. SQL

006 my. cnf description
[Client] # client Configuration
Port = 3306
Socket =/var/lib/mysql. sock # use this socket link on the local client to connect to mysqld
Default_character_set = utf8
Secure_auth = 0 # Skipping incorrect password formats

[Mysqld]
Datadir =/usr/local/mysql/data # specify the data path
Socket =/var/lib/mysql. sock # use this socket to start the server
User = mysql
Old_passwords = 1
Secure_auth = 0
Innodb_force_recovery = 0 # It must be 0; otherwise, innodb is in read only mode.
[Mysqld_safe]
Log-error =/var/log/mysqld. log
Pid-file =/var/run/mysqld. pid

007 change root Password
Mysqld_safe -- skip-grant-tables
Mysql> update mysql. user set password = password ("new_pass") where user = "root ";

008 let the mysql server output all SQL statement records executed

Mysqld -- general-log = TRUE

The log output is the localhost. log of the mysql server data directory. For other logs, run mysqld -- verbose -- help to view them.

Database Export and Import

Export data
Mysqldump-uusername-ppassword databasename> databasename. SQL
Export stored procedures and functions
Mysqldump-uusername-ppassword-ntd-R databasename> stored-procs. SQL
Export only the table structure
Mysqldump-uusername-ppassword-d -- add-drop-table databasename> table-init. SQL
Import SQL files (data, table structure, stored procedure ...)
Mysql-uusername-ppassword databasename <sql-file. SQL

010 check which threads are running

Mysql> show processlist;

You can also view the executed statements in the Info field. If the execution is slow, you can directly query the statements.

011 Delete table records

Delete from tablename

Truncate table tablename

012 SQL secuirty of mysql Stored Procedure
The SQL secuirty In the mysql Stored Procedure determines the security restrictions when executing the relevant stored procedure. The SQL secuirty can be DEFINER or INVOKER.
DEFINER indicates that the user who executes the stored procedure must be the creator of the stored procedure and must have this user and have corresponding permissions.
INVOKER indicates that the user executing the stored procedure must be an existing user and have corresponding permissions.

You can specify SQL secuirty when creating a stored procedure. In the following example, you can specify SQL secuirty as INVOKER.
Create definer = 'admin' @ 'localhost' PROCEDURE account_count ()
SQL SECURITY INVOKER
BEGIN
SELECT 'number of accounts: ', COUNT (*) FROM mysql. user;
END;

You can also set the SQL secuirty by modifying the security_type field in the mysql. proc table. Example
Update mysql. proc set security_type =...

Or

Alter procedure pro_name SQL security invoker;

013 Add a field (time field) to the data table)

Mysql> alter table mytable add column ctime datetime default now () not null after flag;
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0

014 left and right join

Syntax:

SELECT * FROM tableA
LEFT | right join tableB
ON tableA. field1 = tableB. field2
Left JOIN: Based on the left (tableA) table, all tableA and tableB that meet the conditions are found. Records that do not exist in tableB show that the tableB related field is NULL;

Right JOIN: Based on the right (tableB) table, all tableA and tableB that meet the conditions are found. Records that do not exist in tableA show that the tableA field is NULL;

015 Modify column definitions and define Indexes

Alter table table_name modify column_name varchar (32 );
Alter table table_name add index 'column _ name' ('column _ name ');

016 make mysql

Http://dev.mysql.com/doc/internals/en/cmake-build-options-official-mysql.html

• Unix (Makefiles)

  mkdir bldcd bldcmake .. -DBUILD_CONFIG=mysql_releasemake

Note: If the dependency is still prompted after libaio is installed, re-execute the above steps (delete bld)

017 mysqlUser Permissions cannot be granted after upgrade.

2014-05-27 14:48:34 17291 [ERROR] Missing system table mysql. proxies_priv; please run mysql_upgrade to create it
14:48:34 17291 [Warning] Info table is not ready to be used. Table 'mysql. slave_master_info 'cannot be opened.
14:48:34 17291 [Warning] Info table is not ready to be used. Table 'mysql. slave_relay_log_info 'cannot be opened.
14:48:34 17291 [ERROR] Native table 'performance _ scheme'. 'cond _ instances' has the wrong structure
14:48:34 17291 [ERROR] Native table 'performance _ scheme'. 'events _ waits_current 'has the wrong structure
14:48:34 17291 [ERROR] Native table 'performance _ scheme'. 'events _ waits_history 'has the wrong structure
14:48:34 17291 [ERROR] Native table 'performance _ scheme'. 'events _ waits_history_long 'has the wrong structure
14:48:34 17291 [ERROR] Native table 'performance _ scheme'. 'events _ waits_summary_by_host_by_event_name 'has the wrong structure
14:48:34 17291 [ERROR] Native table 'performance _ scheme'. 'events _ waits_summary_by_instance 'has the wrong structure
14:48:34 17291 [ERROR] Native table 'performance _ scheme'. 'events _ waits_summary_by_thread_by_event_name 'has the wrong structure
14:48:34 17291 [ERROR] Native table 'performance _ scheme'. 'events _ waits_summary_by_user_by_event_name 'has the wrong structure
14:48:34 17291 [ERROR] Native table 'performance _ scheme'. 'events _ waits_summary_by_account_by_event_name 'has the wrong structure

Solution: mysql_upgrade-u root-p

018 SQL _mode doesn' t have a default value null

The storage process cannot be imported, and the following error is prompted: solution: Modify SQL _mode in the configuration file, comment out, and restart MySQL.

# SQL _mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES

If you find that the data cannot be inserted due to the default value when executing the stored procedure, you must directly modify the SQL _mode of the stored procedure in the mysql. proc table to NO_ENGINE_SUBSTITUTION.

For more information, see here

You can also directly connect to mysql to execute source/path/to/SQL _file. SQL

019 no MySQL database after mysql Installation

Run./scripts/mysql_install_db in the installation directory.

020 connecting mysql to localhost

After mysql is installed, you can only use localhost as the host to connect to the mysql instance. It cannot be connected to other remote hosts for a long time (the firewall seems to have no restrictions !), It is found that mysqld is started on port 13306, but the database configuration of the application server is 3306, and the connection is still successful. Why?

The reason is that mysql uses a socket file instead of a tcp connection to connect to localhost. Therefore, the specified port is invalid. Port 13306 shown below will be ignored

shell> mysql --port=13306 --host=localhost

If you need to use a TCP connection, you need to specify -- host as different from localhostOr specify a TCP connection.

shell> mysql --port=13306 --host=127.0.0.1shell> mysql --port=13306 --protocol=TCP