LinuxHostingSupport

You need to use the “mysqldump” command to backup a Mysql database.

Backup/Dump a Mysql database on a Linux Or Linux/cPanel server:

root@host [~]# mysqldump /var/lib/mysql/dbname > dbname.sql

Restore the .sql file in a database:

root@host [~]# mysql /var/lib/mysql/dbname < /path/to/filename.sql

Backup/Dump a Mysql database on a Linux/Plesk server:

root@host [~]# mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` /var/lib/mysql/dbname > dbname.sql

Restore the .sql file on a Plesk server :

root@host [~]# mysql -uadmin -p`cat /etc/psa/.psa.shadow` /var/lib/mysql/dbname < /path/to/filename.sql

If you receive the following error message on your website, which means the table is crashed.

“Table “tablename” is marked as crashed and should be repaired”

The Mysql table consists of 3 files, .MYI, .MYD and .frm. The .MYI file contains the structure and results in the error message if something goes wrong with it. There are 3 ways to repair the table:

1)  Login to your cPanel, goto “Mysql Databases” option and click “Repair DB” in front of the databases.

2) Login to your server, goto mysql directory /var/lib/mysql and execute the command:              myisamchk -r dbname/tablename.MYI

3) Login to your server, goto Mysql prompt and execute the Mysql query:                                                         repair table tablename;

Anyone of the above 3 steps will repair the table and site will be back online.

People may think of changing “collation” for their Mysql databases and here how to change it. First, there are two ways to check the current collation on your server. One from the command line and one from the mysql prompt:

root@server [~]# mysqladmin variables | grep collation
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |

OR

mysql> SHOW VARIABLES LIKE ‘collation%’;
+———————-+—————–+
| Variable_name | Value |
+———————-+—————–+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+———————-+—————–+
3 rows in set (0.00 sec)

As you can see the current collation is set to “utf8_general_ci”.

In order to change it to something for example “latin1_general_ci”, edit your /etc/my.cnf file and place the following code:

collation-server=latin1_general_ci

Save the file and restart mysql service.

HowTo GRANT privileges to a Mysql user?

Create a database:

mysql> create database dbname;

and grant the privileges using the following query:

mysql> grant all on dbname.* to ‘dbuser’@'localhost’ identified by ‘password’;

The user ‘dbuser’ will now have access to database ‘dbname’ locally using the password ‘password’. Using the above command, you can also allow new users to access your database as well. Just replace the ‘dbuser’ and ‘password’ with the new user and password.

To allow the user to access the database remotely say from IP 111.222.333.444, execute:

mysql> grant all on dbname.* to ‘dbuser’@’111.222.333.444‘ identified by ‘password’;