MySQL

How to: Connect to MySQL Remotely

mysqlEnabling Plesk 12 Remote MySQL Connections

1. Connect to your server via SSH.

2. Log into MySQL.

mysql -u admin -p`cat /etc/psa/.psa.shadow`

3. If you are attempting to grant non-localhost access to a user, you should use this line:

GRANT ALL PRIVILEGES ON dbname.* TO username@'IP' IDENTIFIED BY 'password';

Where:

  • dbname is replaced by the database you’d like to open up (a * here will open up all databases)
  • username is replaced by the user to be allowed access
  • IP is replaced by the actual IP to connect from (a % here will open up to all IPs — NOT RECOMMENDED).
  • password is replaced by the desired password. A blank field here will result in no password (NOT RECOMMENDED). Changing the password for that user listed in Plesk will set it as well.

4. Apply these changes by using the MySQL command:

FLUSH PRIVILEGES;

5. Next, quit MySQL by using this command:

quit

6. You may need to allow the source IP from which you are connecting to connect to port 3306 after granting the privileges inside MySQL. Connect to your server as “root” and issue the following command:

iptables -I INPUT -s -p tcp --dport 3306 -j ACCEPT

Be sure to replace with your IP address.

How to: Backup all MySQL Databases in Plesk

mysqlSometimes you need to make a dump of all MySQL databases, possibly prior to an upgrade or before you apply a required fix.

I prefer to dump all database as .SQL as an added safety measure, just in case a back out plan is required.

First we create a folder for our database dumps:

# mkdir /root/mysqlbackup

If you are running Plesk, let’s take a dump of the psa database:

# mysqldump -uadmin -p`cat /etc/psa/.psa.shadow ` psa > /root/mysqlbackup/psa.`date +%F_%H.%M`.sql

We certainly want a dump of the mysql database itself:

# mysqldump -uadmin -p`cat /etc/psa/.psa.shadow ` mysql > /root/mysqlbackup/mysql.`date +%F_%H.%M`.sql

Now we can perform a dump of all other databases:

# mysqldump -uadmin -p`cat /etc/psa/.psa.shadow ` --all-databases > /root/mysqlbackup/all.`date +%F_%H.%M`.sql

If you now need to complete a MySQL upgrade (which may have failed prior) you can complete it as follows:

# mysql_upgrade -uadmin -p` cat /etc/psa/.psa.shadow ` --debug-check --debug-info --verbose

Unable to execute SQL: Table ‘./db/wp_comments’ is marked as crashed and should be repaired

How to Fix: Crashed MySQL Database

If your database is marked as crashed and needs to be repaired you may find it will not backup (or migrate) using mysqldump. In these instances you need to login to mysql and run the check/repair process. It’s very easy.

First, you need to authenticate to the mysql server. The example below is for Plesk servers:

# mysql -uadmin -p`cat /etc/psa/.psa.shadow`

Let’s check the table and see the current status:

# mysql> check table db.wp_comments;
+-----------------------------------+-------+----------+-----------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------------------------------+-------+----------+-----------------------------------------------------------+
| db.wp_comments | check | warning | Table is marked as crashed |
| db.wp_comments | check | error | Size of datafile is: 26984448 Should be: 26985708 |
| db.wp_comments | check | error | Corrupt |
+-----------------------------------+-------+----------+-----------------------------------------------------------+
3 rows in set (0.00 sec)

Now we can see the problem, let’s run the REPAIR TABLE facility:

# mysql> repair table db.wp_comments;
+-----------------------------------+--------+----------+-------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------------------------------+--------+----------+-------------------------------------------------------+
| db.wp_comments | repair | info | Found block that points outside data file at 26984408 |
| db.wp_comments | repair | status | OK |
+-----------------------------------+--------+----------+-------------------------------------------------------+
2 rows in set (4.23 sec)

That’s now all fixed. Yes, it’s that easy! Let’s check the table again to be 100% sure:

# mysql> check table db.wp_comments;
+-----------------------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------------------+-------+----------+----------+
| db.wp_comments | check | status | OK |
+-----------------------------------+-------+----------+----------+
1 row in set (0.05 sec)

You can now transfer your MySQL database, dump it or re-migrate it as required.