MySQL FAQs and application Tips

Objective

In MySQL daily development or maintenance, there are some problems or failures are often difficult to avoid, such as lost passwords, table corruption. Summarize the common questions here for future needs.

Forget the root password of MySQL

Workaround:

1. Log in to the database server, manually kill the MySQL process

The command to close the process is: kill 进程号 (Linux)
The command to close the process is: taskkill 进程号 (win down)
How do I see the process number?
We all know that the MySQL process number is usually stored in the MySQL data directory, it records the MySQL service process number. The file name is Hostname.pid, as below, where Yegzt8joiiu2lky is the host name.

Linux view process by using the PS command; under win, we can also view the process number by tasklist.

Open the file, you can see that it only records the process good

Now for convenience, I mainly demonstrate win under operation, not ready to open the virtual machine in the test. If we are unfamiliar with the Taskkill command, you can view the command by Taskkill/?.

2. Restart the MySQL service using the –skip-grant-tables option

Linux under
Syntax:./bin/mysqld_safe–skip-grant-tables
Where the –skip-grant-tables option represents the start of MySQL
(note win under install MySQL, I use mysqld–skip-grant-tables)

3. Connect MySQL with a root user with a blank password and change the root password

Modify the following
The use of the Set password command failed because it was started with –skip-grant-tables.

Update the user table in the MySQL database directly using the UPDATE statement.

4. Update permissions

5. Re-Login

After exiting, found in this use Mysql-uroot login, will login unsuccessful, can only use password login.

Table corruption for processing MyISAM storage engine

In the case of MySQL, you may experience table corruption in the MyISAM storage engine. As in the following cases:

• . frm is locked and cannot be modified
• Cannot find the. myi file (index file)
• I had an unexpected end.
• Log file is destroyed
• Getting error nnn from the table processor
  Solution 1
  Repair using the Myisamchk tool that comes with MySQL
  Open the bin directory to see the tool
  
  The command is as follows
  MYISAMCHK-R TableName
  R stands for recover
  Or
  Myisamchk-o TableName
  The-o parameter means that –safe-recover can be repaired more safely
  Solution 2
  Repair using MySQL's check table and repair table commands
  A check table is used to check for damage to tables, and repair table is used to fix bad tables.

MyISAM table is too large to access the problem

First we can view the MyISAM table by Myisamchk command. For example, I view the admin table

Datefile length represents the current file size
KeyFile length represents the index file size
Max datefile length max file size
Max keyfile length Max index size
You can extend the data file size with the following command
ALTER TABLE table_name max_rows=88888888 avg_row_le=66666

Low Data directory disk space issues for MyISAM storage engine

You can store data directories and index directories in different disk spaces.

For the InnoDB storage engine

For InnoDB storage engine tables, because data files and index files are stored together. So they cannot be separated. When there is insufficient disk space, you can add a new data file that is placed on a disk with sufficient space. The specific implementation is to add this file through Innodb_data_file_path.
Innodb_data_file_path=/home/mysql/data:10000m;/user/mysql/data:10000m:autoextend
After the parameters have been modified, the server needs to be restarted before it can take effect.

Install multiple MySQL on the same host

In addition to each MySQL installation directory can not be the same, but also need port and socket can not be the same.
Mysql.sock is the client connection and the MySQL communication between. Socket file, can only be used natively, remote connection to pass TCP/IP.

Summarize

After a lapse of one months, the party will "MySQL database development, optimization and management maintenance" read. Feel this book is still quite good, very suitable for beginners like me. In the reading process, the feeling benefited a lot.

MySQL FAQs and application Tips