A summary of MySQL

https://blog.atime.me/note/mysql-summary.html

Summarize the various problems encountered during the use of MySQL and some useful resources, configuration and so on. Summarize some of the previous scattered articles together, memo.

If not specifically stated, the content is based on Ubuntu 12.04 and MySQL5.5. For the code that appears in this article, the ... ellipsis is omitted, followed by the # code comment.

Configuration using UTF8 encoding

In the MySQL configuration file, do the following settings:

[client]default-character-set = Utf8[mysqld]character-set-server = UTF8

Then restart the MySQL service, noting that the tables you have created are not affected.

Enable query logging

[Mysqld]...general-log=1general-log-file =/var/log/mysql/general.log ...

Disabling the InnoDB engine

After MySQL5.5 uses InnoDB as the default engine, if it is too memory consuming, you can disable InnoDB using the following configuration and use MyISAM as the default engine.

Add the following two lines to the MY.CNF mysqld block:

[Mysqld]...innodb=offdefault_storage_engine=myisam ...

Then restart the MySQL service and log in to MySQL and use the following command to view the engine status:

mysql> SHOW VARIABLES like '%storage_engine% ';

Length of experience char and varchar

From MySQL 5.X, the number 20 in CHAR (20) and varchar (20) refers to the number of characters, regardless of the encoding. In addition, because MySQL limits the maximum number of rows per row to 65535 bytes, the maximum length and field count of these fields are limited. 1

Forgot root password

First, modify the MySQL configuration file/etc/mysql/my.cnf, add it in the [mysqld] zone skip-grant-tables , and then restart the MySQL service service mysql restart .

Then log on to the root user of MySQL without a password mysql -uroot and execute the following command in turn:

mysql> use MySQL;               # switch to MySQL database mysql> update user set Password=password (' New_password ') where user = ' root '; # New_password is your new password mysql> flush privileges;        # Refresh System Permissions

Finally, modify the configuration file/etc/mysql/my.cnf, and restart the skip-grant-tables MySQL service by deleting the deletion that was just added in [mysqld].

What type of thinking date is stored

In the MySQL database, dates can be stored in multiple types, and the following are the pros and cons of the various types I think of today.

• MySQL date and time Types:mysql built-in dates and times, the advantage is that MySQL has a lot of built-in date and time functions to use, the disadvantage is that the portability is poor.
• char or varchar: no merit for the moment.
• INT: The advantage is good portability, you can do some basic comparison operation, the disadvantage is that MySQL's built-in date and time type ratio, the available functions are very few; In addition, the int type cannot store time zone information.

Problem error 1175 Safe updtes Mode

The error prompts are as follows:

ERROR 1175:you is using Safe update mode and you tried to update a table without a WHERE that uses a KEY column.

The reason for the error is that the safe updtes Mode with MySQL is enabled, and its function is that if the update and delete operations are performed without a where statement or a limit statement with a key restriction, the SQL operation will not execute. Refer to MySQL tips:safe-updates for a detailed introduction.

Can be used SET sql_safe_updates=0; to temporarily disable safe Updates Mode.

Stopping MySQL database server:mysqld failed

Environment

• Os:debian6 Squeeze
• mysql:5.1.63

service mysql restart Stopping MySQL database server: mysqld failed! The MySQL service cannot be restarted because it is displayed using the command.

Workaround

The problem is caused by the Debian-sys-maint user's password in the MySQL database and the password inconsistency in the/ETC/MYSQL/DEBIAN.CNF, which can be resolved by referring to this article. The following steps are described.

1. Look at the password in/etc/mysql/debian.cnf, cat /etc/mysql/debian.cnf there are two password, but the values are the same.
2. Connect to MySQL,mysql -uroot -p

3. Change the password for the debian-sys-maint user.

   GRANT all privileges on . To ' debian-sys-maint ' @ ' localhost ' identified by with <password> GRANT OPTION;

Cause of the problem

Prior to migrating the site using the backup script, only/etc/mysql/debian.cnf was migrated and no MySQL database was migrated in MySQL, resulting in debian-sys-maint passwords and/etc/in the user table in the MySQL database. MYSQL/DEBIAN.CNF inconsistent.

Snippets

Take database db and table TB for example.

Management-related

Show all databases

mysql> show databases;

Show all tables for the current database

Mysql> Show tables;

Switch database

mysql> Use DB

Show the structure of a table

mysql> desc TB;

To display database creation information

Mysql> show CREATE database db;

To display the creation statement for a table

Mysql> Show CREATE TABLE TB;

Back up a table

$ mysqldump-u $user-P $db $table > Table.sql

Table Management

Modifying the character set of a table

mysql> ALTER TABLE ' db '. ' TB ' CHARACTER SET UTF8;

Modify the character set of the field, refer to column Character set Conversion.

View mydb the size (including index) of individual tables in the database, sorted in reverse order of size

Mysql> Select table_name as "Tables",       Round ((((Data_length + index_length)/1024/1024), 2) as "Size in MB"       FR OM information_schema. TABLES       WHERE table_schema = "MyDB"       ORDER by (data_length + index_length) DESC;

Permission-related

See MySQL account Management SQL for detailed information.

Show permissions

mysql> SHOW GRANTS for ' user_name ' @ ' host ';

assigning permissions

Mysql> GRANT all privileges on ' database '. Table ' to ' user_name ' @ ' host ' identified by ' password ';mysql> FLUSH privileges;

Release permissions

Mysql> REVOKE all privileges on ' database '. Table ' from ' user_name ' @ ' host ';mysql> FLUSH privileges;

Delete a table in bulk

Bulk Delete tables with the same prefix, use the following SQL statement to construct the corresponding drop statement, and delete the wp_ table prefixed by the WORDPRESS3 database.

SELECT CONCAT (' DROP TABLE IF EXISTS wordpress3. ', table_name, '; ') From Information_schema.tableswhere table_schema = ' WORDPRESS3 ' and table_name like ' wp_% ';

To delete the steps below, first generate the Delete command and save it to the Drop.sql file.

Mysql-uroot-anspe "Select CONCAT (' DROP TABLE IF EXISTS wordpress3. ', table_name, '; ')                       From Information_schema.tables                       WHERE table_schema = ' WORDPRESS3 ' and table_name like ' wp_% '; "> Drop.sql

Then check that the statements in the Drop.sql file are correct, and if so, then execute the following command. (Be sure to back up the relevant data before executing the delete command )

Mysql-uroot-p-E "source Drop.sql"

Other

Refer to this issue for converting blobs to specific encoded strings.

SELECT CONVERT (Blob_column USING GBK) from table_name;

A summary of MySQL