How to repair MySQL databases and tables

This article describes how to repair MySQL databases and tables. As a database's tables grow, errors may occur from time to time. When they do, MySQL includes several tools that you can use to check and repair database tables. To do this, follow the procedures below in the order in which they appear.

This article only applies to products listed in the Article Details sidebar. You must have root access to the server to follow these procedures.

Table of Contents

• Step 1: Backing up the databases
• Step 2: Checking and repairing a table with mysqlcheck
• Step 3: Running engine-specific diagnostics
  • Repairing MyISAM tables with myisamchk
  • Running the InnoDB recovery process
• More Information
• Related Articles

STEP 1: BACKING UP THE DATABASES

Before you attempt to repair any database, you should back it up first. To back up all of the files from all of your databases, follow these steps:

1. Log in to your server using SSH.
2. Stop the MySQL server using the appropriate command for your Linux distribution:
   • For CentOS and Fedora, type:

     service mysqld stop

   • For Debian and Ubuntu, type:

     service mysql stop

3. Type the following command:

   cp -rfv /var/lib/mysql /var/lib/mysql$(date +%s)

   This command copies all of the files from all of your databases to a directory name based on the current time (more precisely, the number of seconds elapsed since January 1, 1970). This ensures that each database backup is stored in a directory that has a unique name. For added protection, you can (and should) back up the database files to a remote location not on the server.
4. Restart the MySQL server using the appropriate command for your Linux distribution:
   • For CentOS and Fedora, type:

     service mysqld start

   • For Debian and Ubuntu, type:

     service mysql start

STEP 2: CHECKING AND REPAIRING A TABLE WITH MYSQLCHECK

After you back up your databases, you are ready to start troubleshooting. The mysqlcheckprogram enables you to check and repair databases while MySQL is running. This feature is useful when you want to work on a database without stopping the entire MySQL service.

Additionally, mysqlcheck works on tables that use the MyISAM or InnoDB database engines.

For information about how to determine which storage engine a database table is using, please see this article.

To use mysqlcheck, follow these steps:

1. As the root user, type the following command:

   cd /var/lib/mysql

2. Type the following command, replacing DATABASE with the name of the database that you want to check:

   mysqlcheck DATABASE

   The previous command checks all of the tables in the specified database. Alternatively, to check a specific table in a database, type the following command. Replace DATABASE with the name of the database, and replace TABLE with the name of the table that you want to check:

   mysqlcheck DATABASE TABLE

3. Mysqlcheck checks the specified database and tables. If a table passes the check, mysqlcheck displays OK for the table. However, if mysqlcheck reports an error for a table, type the following command to try to repair it. Replace DATABASE with the database name, and TABLE with the table name:

   mysqlcheck -r DATABASE TABLE

4. If mysqlcheck cannot successfully repair the table or tables, go to the following procedure.

STEP 3: RUNNING ENGINE-SPECIFIC DIAGNOSTICS

If running mysqlcheck does not fix the problem, the next step is to run diagnostics specific to the engine used by the database table or tables. Follow the appropriate procedure below for your table's database storage engine.

For information about how to determine which storage engine your database tables are using, please see this article.

Repairing MyISAM tables with myisamchk

If you are using the MyISAM storage engine for a table, you can run the myisamchkprogram to repair it. To do this, follow these steps:

The myisamchk program only works for tables that use the MyISAM storage engine. It does not work for the InnoDB engine.

1. Stop the MySQL server using the appropriate command for your Linux distribution:
   • For CentOS and Fedora, type:

     service mysqld stop

   • For Debian and Ubuntu, type:

     service mysql stop

2. Type the following command:

   cd /var/lib/mysql

3. Change to the directory where the database is located. For example, if the database is named customers, type cd customers.
4. Type the following command, replacing TABLE with the name of the table that you want to check:

   myisamchk TABLE

   To check all of the tables in a database, type the following command:

   myisamchk *.MYI

   If the previous command does not work, you can try deleting temporary files that may be preventing myisamchk from running correctly. To do this, change back to the /var/lib/mysql directory, and then type the following command:

   ls */*.TMD
   

   If there are any .TMD files listed, type the following command to delete them:

   rm */*.TMD

   Then try to run myisamchk again.
5. To try to repair a table, type the following command, replacing TABLE with the name of the table that you want to repair:

   myisamchk --recover TABLE

6. Restart the MySQL server using the appropriate command for your Linux distribution:
   • For CentOS and Fedora, type:

     service mysqld start

   • For Debian and Ubuntu, type:

     service mysql start

7. Test the repaired table or tables.

Running the InnoDB recovery process

If you are using the InnoDB storage engine for a database table, you can run the InnoDB recovery process. To do this, follow these steps:

1. Use your preferred text editor to open the my.cnf file on your server. The location of the my.cnf file depends on your Linux distribution:
   • On CentOS and Fedora, the my.cnf file is located in the /etc directory.
   • On Debian and Ubuntu, the my.cnf file is located in the /etc/mysql directory.
2. In the my.cnf file, locate the [mysqld] section.
3. Add the following line to the [mysqld] section:

   innodb_force_recovery=4

4. Save the changes to the my.cnf file, and then restart the MySQL server using the appropriate command for your Linux distribution:
   • For CentOS and Fedora, type:

     service mysqld restart

   • For Debian and Ubuntu, type:

     service mysql restart

5. Type the following command to export all of the databases to the databases.sql file:

   mysqldump --all-databases --add-drop-database --add-drop-table > databases.sql

6. Start the mysql program, and then try to drop the affected database or databases using the DROP DATABASE command.
   If MySQL is unable to drop a database, you can delete it manually in step 8 below after you stop the MySQL server.
7. Stop the MySQL server using the appropriate command for your Linux distribution:
   • For CentOS and Fedora, type:

     service mysqld stop

   • For Debian and Ubuntu, type:

     service mysql stop

8. If you were unable to drop a database in step 6, type the following commands to delete it manually. Replace DBNAME with the name of the database that you want to delete:

   cd /var/lib/mysql
   rm -rf DBNAME

   Make sure you do not delete the mysql or performance_schemadirectories!
9. Use your preferred text editor to open the my.cnf file on your server, and then comment out the following line in the [mysqld] section as shown:

   #innodb_force_recovery=4

   This disables InnoDB recovery mode.
10. Save the changes to the my.cnf file, and then start the MySQL server using the appropriate command for your Linux distribution:
    • For CentOS and Fedora, type:

      service mysqld start

    • For Debian and Ubuntu, type:

      service mysql start

11. Type the following command to restore the databases from the backup file you created in step 5:

    mysql < databases.sql

12. Test the restored database.

MORE INFORMATION

• For more information about mysqlcheck, please visit https://dev.mysql.com/doc/refman/5.5/en/mysqlcheck.html.
• For more information about myisamchk, please visit https://dev.mysql.com/doc/refman/5.5/en/myisamchk.html.

RELATED ARTICLES

• Managing MySQL databases and users from the command line
  Learn how to do common MySQL administration tasks from the command line. Please note that this article only applies to semi-managed products.
• Working with MySQL database engines
  Learn how to determine the database engines your tables use, how to change engines, and much more.

How to start / stop / restart / enable / reload the MySQL & MariaDB server in Linux

BY MAGESH MARUTHAMUTHU · PUBLISHED : OCTOBER 9, 2013 || LAST UPDATED: JANUARY 23, 2016

Systemd is a new init system and system manager which was implemented/adapted into all the major Linux distributions over the traditional SysV init systems due to lots of issue/improvement has to be on sysVinit systems. All the services is available “/etc/init.d/. If you want to start, stop, restart, enable, reload & status of the service follow the below commands to do it. Either root or sudo Permission needed to run the below commands.

1) To Start MySQL/MariaDB server

Use the below commands to start the MySQL/MariaDB server in Linux.

# For Sysvinit Systems #
# service mysql start
or
# /etc/init.d/mysql start

# For Systemd Systems #
# systemctl start mariadb.service
or
# systemctl start mysql.service
or
# systemctl start mariadb
or
# systemctl start mysql

2) To Stop MySQL/MariaDB server

Use the below commands to stop the MySQL/MariaDB server in Linux.

# For Sysvinit Systems #
# service mysql stop
or
# /etc/init.d/mysql stop

# For Systemd Systems #
# systemctl stop mariadb.service
or
# systemctl stop mysql.service
or
# systemctl stop mariadb
or
# systemctl stop mysql

3) To Restart MySQL/MariaDB server

Use the below commands to restart the MySQL/MariaDB server in Linux.

# For Sysvinit Systems #
# service mysql restart
or
# /etc/init.d/mysql restart

# For Systemd Systems #
# systemctl restart mariadb.service
or
# systemctl restart mysql.service
or
# systemctl restart mariadb
or
# systemctl restart mysql

4) To Reload MySQL/MariaDB server

Use the below commands to reload the MySQL/MariaDB server in Linux.

# For Sysvinit Systems #
# service mysql reload
or
# /etc/init.d/mysql reload

# For Systemd Systems #
# systemctl reload mariadb.service
or
# systemctl reload mysql.service
or
# systemctl reload mariadb
or
# systemctl reload mysql

5) To view status of MySQL/MariaDB server

Use the below commands to view the status of MySQL/MariaDB server in Linux.

# For Sysvinit Systems #
# service mysql status
or
# /etc/init.d/mysql status

# For Systemd Systems #
# systemctl status mariadb.service
or
# systemctl status mysql.service
or
# systemctl status mariadb
or
# systemctl status mysql

6) To Enable MySQL/MariaDB server in boot

Use the below commands to enable the MySQL/MariaDB server in boot. In sysVinit system we can use Chkconfig command to configure services in boot via /etc/rd.d/init.d script & Systemd system via /usr/lib/systemd/system script.

# For Sysvinit Systems #
# chkconfig mysqld on

# For Systemd Systems #
# systemctl enable mariadb.service
or
# systemctl enable mysql.service
or
# systemctl enable mariadb
or
# systemctl enable mysql

MySQL: Joins

This MySQL tutorial explains how to use MySQL JOINS (inner and outer) with syntax, visual illustrations, and examples.

Description

MySQL JOINS are used to retrieve data from multiple tables. A MySQL JOIN is performed whenever two or more tables are joined in a SQL statement.

There are different types of MySQL joins:

• MySQL INNER JOIN (or sometimes called simple join)
• MySQL LEFT OUTER JOIN (or sometimes called LEFT JOIN)
• MySQL RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)

So let's discuss MySQL JOIN syntax, look at visual illustrations of MySQL JOINS, and explore MySQL JOIN examples.

INNER JOIN (simple join)

Chances are, you've already written a statement that uses a MySQL INNER JOIN. It is the most common type of join. MySQL INNER JOINS return all rows from multiple tables where the join condition is met.

Syntax

The syntax for the INNER JOIN in MySQL is:

SELECT columns
FROM table1 
INNER JOIN table2
ON table1.column = table2.column;

Visual Illustration

In this visual diagram, the MySQL INNER JOIN returns the shaded area:

The MySQL INNER JOIN would return the records where table1 and table2 intersect.

Example

Here is an example of a MySQL INNER JOIN:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers 
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

This MySQL INNER JOIN example would return all rows from the suppliers and orders tables where there is a matching supplier_id value in both the suppliers and orders tables.

Let's look at some data to explain how the INNER JOINS work:

We have a table called suppliers with two fields (supplier_id and supplier_name). It contains the following data:

supplier_id	supplier_name
10000	IBM
10001	Hewlett Packard
10002	Microsoft
10003	NVIDIA

We have another table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data:

order_id	supplier_id	order_date
500125	10000	2013/05/12
500126	10001	2013/05/13
500127	10004	2013/05/14

If we run the MySQL SELECT statement (that contains an INNER JOIN) below:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

Our result set would look like this:

supplier_id	name	order_date
10000	IBM	2013/05/12
10001	Hewlett Packard	2013/05/13

The rows for Microsoft and NVIDIA from the supplier table would be omitted, since the supplier_id's 10002 and 10003 do not exist in both tables. The row for 500127 (order_id) from the orders table would be omitted, since the supplier_id 10004 does not exist in the suppliers table.

Old Syntax

As a final note, it is worth mentioning that the MySQL INNER JOIN example above could be rewritten using the older implicit syntax as follows (but we still recommend using the INNER JOIN keyword syntax):

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;

LEFT OUTER JOIN

Another type of join is called a MySQL LEFT OUTER JOIN. This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

Syntax

The syntax for the LEFT OUTER JOIN in MySQL is:

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

In some databases, the LEFT OUTER JOIN keywords are replaced with LEFT JOIN.

Visual Illustration

In this visual diagram, the MySQL LEFT OUTER JOIN returns the shaded area:

The MySQL LEFT OUTER JOIN would return the all records from table1 and only those records from table2 that intersect with table1.

Example

Here is an example of a MySQL LEFT OUTER JOIN:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
LEFT JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

This LEFT OUTER JOIN example would return all rows from the suppliers table and only those rows from the orders table where the joined fields are equal.

If a supplier_id value in the suppliers table does not exist in the orders table, all fields in the orders table will display as <null>in the result set.

Let's look at some data to explain how LEFT OUTER JOINS work:

We have a table called suppliers with two fields (supplier_id and supplier_name). It contains the following data:

supplier_id	supplier_name
10000	IBM
10001	Hewlett Packard
10002	Microsoft
10003	NVIDIA

We have a second table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data:

order_id	supplier_id	order_date
500125	10000	2013/05/12
500126	10001	2013/05/13

If we run the SELECT statement (that contains a LEFT OUTER JOIN) below:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
LEFT JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

Our result set would look like this:

supplier_id	supplier_name	order_date
10000	IBM	2013/05/12
10001	Hewlett Packard	2013/05/13
10002	Microsoft	<null>
10003	NVIDIA	<null>

The rows for Microsoft and NVIDIA would be included because a LEFT OUTER JOIN was used. However, you will notice that the order_date field for those records contains a <null> value.

RIGHT OUTER JOIN

Another type of join is called a MySQL RIGHT OUTER JOIN. This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

Syntax

The syntax for the RIGHT OUTER JOIN in MySQL is:

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;

In some databases, the RIGHT OUTER JOIN keywords are replaced with RIGHT JOIN.

Visual Illustration

In this visual diagram, the MySQL RIGHT OUTER JOIN returns the shaded area:

The MySQL RIGHT OUTER JOIN would return the all records from table2 and only those records from table1 that intersect with table2.

Example

Here is an example of a MySQL RIGHT OUTER JOIN:

SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers
RIGHT JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

This RIGHT OUTER JOIN example would return all rows from the orders table and only those rows from the suppliers table where the joined fields are equal.

If a supplier_id value in the orders table does not exist in the suppliers table, all fields in the suppliers table will display as <null> in the result set.

Let's look at some data to explain how RIGHT OUTER JOINS work:

We have a table called suppliers with two fields (supplier_id and supplier_name). It contains the following data:

supplier_id	supplier_name
10000	Apple
10001	Google

We have a second table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data:

order_id	supplier_id	order_date
500125	10000	2013/08/12
500126	10001	2013/08/13
500127	10002	2013/08/14

If we run the SELECT statement (that contains a RIGHT OUTER JOIN) below:

SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers
RIGHT JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

Our result set would look like this:

order_id	order_date	supplier_name
500125	2013/08/12	Apple
500126	2013/08/13	Google
500127	2013/08/14	<null>

The row for 500127 (order_id) would be included because a RIGHT OUTER JOIN was used. However, you will notice that the supplier_name field for that record contains a <null> value.

SELECT users.phoneNumber, users.userId, pendingUserAssociations.timeRequested FROM users INNER JOIN pendingUserAssociations ON users.userId = pendingUserAssociations.userId WHERE users.userId = 48;

https://www.techonthenet.com/mysql/joins.php