FAQ about MySQL

MySQL always crashes

First, you should try to find out whether the problem mysqld daemon is dead or whether your problem is related to your customer. You can use mysqladmin version to check how long your mysqld server has been running normally. If mysqld is dead, you can go to the file "mysql-data-directory/'hostname '. find the cause in err.

Some common errors when using MySQL

MySQL server has gone away

The common cause is that the server times out and the connection is closed. By default, if nothing happens, the server closes the connection eight hours later. You can change the time limit by setting the wait_timeout variable when starting mysqld.

If mysqld gets a large or abnormal package, it considers the customer has an error and closes the connection.

Can't connect to [local] MySQL Server

It usually means that no MySQL server is running on the system or you are using an incorrect socket file or TCP/IP Port when trying to connect to the mysqld server.

Check (using PS) whether a process named mysqld is started on the server

If a mysqld process is running, you can check the server by trying these different connections.

Shell> mysqladmin version

Shell> mysqladmin Variables

Shell> mysqladmin-H 'hostname' version Variables

Shell> mysqladmin-H 'hostname' -- Port = 3306 version

Shell> mysqladmin-H 'IP for your host' version

Shell> mysqladmin -- socket =/tmp/mysql. sock version

Note that the hostname command uses the reverse quotation mark (') instead of the forward quotation mark ('). These causes the hostname output (that is, the current host name) to be replaced by the mysqladmin command.

Host '...' is blocked Error

Host 'hostname' is blocked because of specified connection errors.

Unblock with 'mysqladmin flush-hosts'

This means that mysqld has obtained a large number of connection requests that are interrupted in the middle of the host 'hostname' (max_connect_errors. After max_connect_errors failed requests, mysqld identified an error (like a hacker attack) and blocked the site from further connection until someone executed the command mysqladmin flush-hosts.

By default, mysqld blocks a host after 10 connection errors. You can easily adjust the server by starting it like this:

Shell> safe_mysqld-O max_connect_errors = 10000 &

Too internal connections Error

This means that max_connections clients have connected to the mysqld server.

If you need more connections than the default value (100), restart mysqld and use a larger max_connections variable value.

Out of memory error

Mysql: Out of memory at line 42, 'malloc. c'

Mysql: needed 8136 byte (8 k), memory in use: 12481367 bytes (12189 k)

ERROR 2008: MySQL client ran out of memory

Note that the error points to MySQL. The reason for this error is that the customer does not have enough memory to store all results.

First, check whether your query is correct.

Packet too large error

A MySQL client or mysqld server obtains a packet longer than max_allowed_packet.

You can use mysql -- set-variable = max_allowed_packet = 8 m to specify a larger buffer to start the client program.

The table is full error

This error occurs when the temporary memory table becomes larger than tmp_table_size.

Commands out of sync in client Error

Calling the customer function in the wrong order!

Ignoring user error

Found wrong password for user: _ user @ some_host '"> 'some _ user @ some_host'; Ignoring user

This means that when mysqld is started or when it loads the permission table again, it finds an entry with an invalid password in the user table. As a result, the entries are ignored by the permission system.

Table 'xxx' doesn' t exist Error

The database and table name are case sensitive! You can use show tables to check which table you have in the current database.

Run SQL commands from a text file

You can put the SQL command in a file and tell mysql to read the input from the file: Create a text file "text_file", which contains the command to be executed. Then call mysql as follows:

Shell> mysql database <text_file

Or

Shell> mysql <text_file

Start a text file with the USE db_name statement.

How to reset a forgotten password

If you forget the password of the root user of MySQL, use the following methods to restore the password:

By sending a kill (not kill-9) to the mysqld server, you can disable the mysqld server. Pid is saved in A. pid file, usually in the MySQL database directory:

Kill 'cat/mysql-data-directory/hostname. Pi'

You must be a UNIX root user or the same user on the running server.

Use the -- skip-grant-tables option to restart mysqld.

Use mysql-h hostname mysql to connect to the mysqld server and use a GRANT command to change the password. See section 7.26 GRANT and REVOKE syntax. You can also use mysqladmin-h hostname-u user password 'new password.

Use mysqladmin-h hostname flush-privileges or the SQL command FLUSH PRIVILEGES to load the permission table.

Question about using the DATE column

The format of the DATE value is 'yyyy-MM-DD '.

Change the sequence of columns in a table

In an application, SELECT * should never be used to retrieve columns based on their locations, because the order of returned columns is never guaranteed; A simple change to the database may cause dramatic application failures.

You can change it as follows:

Create a new table in the correct column order.

Execute insert into new_table select fields-in-new_table-order from old_table.

Delete or rename old_table.

Alter table new_table rename old_table.

Database Replication

MySQL (to date) has no database replication, but there is some information on how to implement it.

The most common method to copy a database is to use the Update log.

Database Backup

In order to get a consistent backup, make a lock tables on the relevant table. You only need one read lock. When you copy a file in the database directory, this allows other threads to continue querying the table. If you want to make an SQL-level backup, you can select into OUTFILE.

Another way to back up a database is to use the mysqldump program:

Make a complete backup for your database:

Shell> mysqldump -- tab =/path/to/Some/dir -- Opt -- full

　
You can also simply copy all the table files ("*. frm", "*. MYD", and "*. myi" files) as long as the server does not update anything.

Stop mysqld if it is running and start it with the -- log-update option. You will get a name named "hostname. N "form of log files, Here N is a number that increments with every execution of mysqladmin refresh, mysqladmin flush-logs, flush logs statements, or restart the server. These log files provide you with the information you need to copy the database changes after you execute mysqldump.

If you must restore something, first use myisamchk-r to restore your table. This should handle 99.9% of all cases. If myisamchk fails, try the following procedure:

Restore the original mysqldump backup.

Run the following command to re-run the changes in the Update log:

Shell> ls-1-t-r hostname. [0-9] * | xargs cat | mysql

Ls is used to obtain all log files in the correct order.

You can also make a selected backup with SELECT * into outfile 'file _ name' FROM tbl_name and use load data infile 'file _ name' REPLACE... to restore. To avoid repeated records, you need a primary key or unique key in the table. When a new record on a unique key value is duplicated with an old record, the REPLACE keyword allows the old record to be replaced by a new record.