MySQL FAQ and application skills summary, mysql application skills

MySQL FAQ and application skills summary, mysql application skills

Preface

In the daily development or maintenance of MySQL, it is often difficult to avoid some problems or faults, such as lost passwords and table damage. Summarize the common problems for future use.

1. Forget the MySQL root Password

1. log on to the server where the database is located and manually kill the mysql process.

(1) log on to the server where the database is located and manually kill the MySQL process:

root@bogon:/data/mysql# kill `cat ./mysql.pid`

Mysql. pid indicates the pid file in the MySQL data directory, which records the process Number of the MySQL service.

(2) Use the -- skip-grant-tables option to restart the MySQL service:

zj@bogon:/data/mysql$ sudo /usr/local/mysql/bin/mysqld --skip-grant-tables --user=root &

The -- skip-grant-tables option indicates that the permission table authentication is skipped when the MySQL service is started. After MySQL is started, the root user connected to MySQL does not need a password.

(3) Use the root user with an empty password to connect to mysql and change the root password:

zj@bogon:/usr/local/mysql/bin$ mysql -urootWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.7.18-log Source distributionCopyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MySQL [(none)]> set password = password('123456');ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statementMySQL [(none)]> use mysqlDatabase changedMySQL [mysql]> update user set authentication_string=password('123456') where user="root" and host="localhost";Query OK, 1 row affected, 1 warning (0.02 sec)Rows matched: 1 Changed: 1 Warnings: 1MySQL [mysql]> flush privileges;Query OK, 0 rows affected (0.00 sec)MySQL [mysql]> exit;Bye****************************************************************zj@bogon:/usr/local/mysql/bin$ mysql -uroot -p123456mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 7Server version: 5.7.18-log Source distributionCopyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MySQL [(none)]> 

Because the -- skip-grant-tables option is used for startup, an error occurred while using the "set password" command to change the password. The authentication_string of the user table is directly updated (the test version is 5.7.18, in some versions and passwords, the password is changed successfully. Refresh the permission table to make the permission authentication take effect again. When you log on again with the root user, you can use the modified password.

Ii. How to Deal with myisam storage engine Table Corruption

Sometimes the myisam table may be damaged. A corrupted table is typically characterized by unexpected query interruptions and the following error is displayed:

• 'Table _ name. frm' is locked and cannot be changed.
• The file 'tbl _ name. myyi' (errcode: nnn) cannot be found)
• File ended unexpectedly
• The record file is destroyed.
• The error nnn is obtained from the table processor.

There are usually two solutions:

1. Use the myisamchk Tool

Use the myisamchk tool that comes with MySQL to fix the problem:

shell> myisamchk -r tablename

The-r parameter indicates recover. The above method can solve almost all problems. If not, run the following command:

shell> mysiamchk -o tablename

Here, the-o parameter is -- safe-recover, which can be used for more secure repairs.

2. Use SQL commands

Use the check table and repair table commands of MySQL to fix the problem. check table is used to check whether damage exists. repair table is used to fix bad tables.

Iii. Insufficient disk space for Data Directories

After the system goes online, as the amount of data increases, it will find that the available space in the data directory is getting smaller and smaller, thus posing a security risk to the application.

1. For tables of the myisam storage engine

For tables of the myisam storage engine, you can use the following options to create data directories and index directories for storage to different disk spaces. By default, tables are stored in the data directory:

data directory = 'absolute path to directory'index directory = 'absolute path to directory'

If a table has been created, you can stop the table or lock the table to prevent changes to the table. Then, you can mv the table data files and index files to a sufficient partition on the disk, create a symbolic link in the original file.

2. For innodb Storage engine tables

Because data files and index files are stored together, they cannot be separated. When the disk space is insufficient, you can add a new data file, which is placed on a disk with sufficient space.

The specific implementation method is to add this file to the innodb_data_file_path parameter, and write the path to the absolute path of the new disk.

For example, if the space in/home is insufficient and you want to add a new file under/home1 that can automatically expand the data, you can write the parameter as follows:

innodb_data_file_path = /home/ibdata1:2000M;/home1/ibdata2:2000M:autoextend

After the parameter is modified, the database must be restarted to take effect.

Iv. DNS reverse resolution (reverse DNS resolution is skipped by default in Versions later than 5.0)

When you execute the show processlist command on the client, many processes sometimes appear, similar:

unauthenticated user | 192.168.10.10:55644 | null | connect | null | login | null

These processes will accumulate more and more, and will not disappear. applications cannot properly respond, resulting in system paralysis.

By default, MySQL performs reverse resolution on the remotely connected IP address. If the hosts file of the system does not contain the corresponding domain name, mySQL considers this connection as an invalid user, so the unauthenticated user appears in the process and the process is blocked.

The solution is simple. When the -- skip-name-resolve option is added at startup, MySQL can skip the domain name resolution process to avoid the above problems.

V. How to connect to the database after mysql. sock is lost

When you connect to the database on the MySQL server, mysql. sock often does not exist, resulting in connection failure. This is because if localhost is specified as a host name, mysqladmin uses a Unix socket file connection by default, instead of a TCP/IP. This socket file (usually named mysql. sock) is often deleted for various reasons. With the -- protocol = TCP | SOCKET | PIPE | MEMORY option, you can explicitly specify the connection protocol. The following example demonstrates the success of tcp connection after a Unix SOCKET fails.

1. Unix socket connection:

zj@bogon:~$ mysqlERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

2. tcp Connection

zj@bogon:~$ mysql --protocol=TCP

6. The MyISAM table is too large to be accessed

First, we can use the myisamchk command to view the MyISAM table. For example, view the admin table

• Datefile length indicates the current file size.
• Keyfile length indicates the size of the index file.
• Max datefile length Maximum File Size
• Max keyfile length maximum index size

You can use the following command to expand the data file size:

alter table table_name MAX_ROWS=88888888 AVG_ROW_LE=66666

VII. Insufficient disk space in the Data Directory

For the MyISAM storage engine

You can store data directories and index directories to 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 the disk space is insufficient, you can add a new data file, which 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 parameter is modified, you must restart the server to make the modification take effect.

8. Install Multiple Mysql instances on the same host

In addition to the installation directory of each Mysql, the port and socket cannot be the same.

Mysql. sock is used for communication between clients and mysql. The socket file can only be used on the local machine. The remote connection must pass through TCP/IP.

Summary

The above is all the content of this article. I hope the content of this article has some reference and learning value for everyone's learning or work. If you have any questions, please leave a message to us, thank you for your support.