LinuxHostingSupport

Somtimes you may want to change the Mysql table from MyISAM to InnoDB engine to setup foreign keys, to use row level locks, improve performace etc.

The conversion of the MyISAM table to InnoDB is easy however, if the table is setup with “FULLTEXT indexes”, it cannot be converted as this feature is not supported in InnoDB.

If a table is setup with “FULLTEXT indexes”, the conversion of table to InnoDB will result in “The used table type doesn’t support FULLTEXT indexes” error message.

mysql> ALTER TABLE test ENGINE=InnoDB;
ERROR 1214: The used table type doesn't support FULLTEXT indexes

The solution is to remove “FULLTEXT indexes” from the table before converting to InnoDB. To check if the table is setup with FULLTEXT indexes, execute:

mysql> show create table test;
 ------------------------
 | Table | Create Table
 ------------------------
 | test | CREATE TABLE `test` (
 `col_name` varchar(10) DEFAULT NULL,
 FULLTEXT KEY `keyname` (`col_name`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

If FULLTEXT is setup, the output of the above command will display a line as follows:

FULLTEXT KEY `keyname` (`col_name`)

Now, remove “FULLTEXT” indexes from the table:

mysql> ALTER TABLE test DROP INDEX keyname;

Now, this table can be converted to InnoDB using the following command:

mysql> ALTER TABLE test ENGINE=InnoDB;
 Query OK, 0 rows affected (0.04 sec)
 Records: 0  Duplicates: 0  Warnings: 0

After performing a mysql upgrade, you may run into a problem where Mysql won’t start successfully and will result in a “Table ‘mysql.plugin’ doesn’t exist” error message. The mysql error logs will state something like follows:

/usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist
110620  01:01:01 [ERROR] Can't open the mysql.plugin table.
Please run mysql_upgrade to create it.

The solution is to run mysql_upgrade to create the mysql.plugin table but the problem is you can execute the command only when Mysql is online. Tricky situation, isn’t it?

This mostly likely happens when mysql is still using the old my.cnf file after the upgrade. The my.cnf of the new Mysql version is saved as /etc/my.cnf.rpmnew and this is the file you should be using

# cp /etc/my.cnf /etc/my.cnf.old
# cp /etc/my.cnf.rpmnew /etc/my.cnf
# /etc/init.d/mysqld start

Once Mysql is started, you can now execute mysql_upgrade:

# mysql_upgrade -uroot -p <mysqlpass>

This will create the ‘mysql.plugin’ table and will make sure the mysql database is uptodate as per the newer version.

By default, Mysql doesn’t allow remote connections for security reasons. However, sometimes you or your client may want to acess the database remotely i.e. from a different server Or from home Or a developer who is working on the website.

To grant remote access to a Mysql databas, the first thing is to make sure ‘skip-networking’ is not enabled in Mysql configuration i.e. in the my.cnf file.

On a CentOS/RHEL server, the file is located at /etc/my.cnf
On a Debian/Ubuntu server, the file is located at /etc/mysql/my.cnf

1) SSH to the server and comment/remove the line from my.cnf that says:

skip-networking

where,
        skip-networking means don’t listen on TCP/IP connections but only from Unix sockets. This is recommended on systems where only local connections are allowed. However, as you need to allow remote connections, skip-networking must be removed/commented in my.cnf as stated above.

2) Save the file and restart the mysql service

# /etc/init.d/mysqld restart

3) Connect to the Mysql server to grant access to a remote IP address.

# mysql -uroot -p

4) Now, for example, if you want to allow access to database ‘db_name’ for user ‘db_user’ and the remote IP is 1.1.1.1, the mysql command should be as follows:

mysql> use mysql
mysql> GRANT ALL ON db_name.* TO 'db_user'@'1.1.1.1' IDENTIFIED\
BY 'PASSWORD';

If your IP is NOT static and you want to grant access to the database from any IP, use % instead of IP, i.e.

mysql> GRANT ALL ON db_name.* TO 'db_user'@'%' IDENTIFIED\
BY 'PASSWORD';

5) Logout of Mysql and make sure port 3306 is open in your server firewall. Test the remote mysql connection from your local computer Or a different server:

# mysql -u db_user -h serverIP -D db_name -p

where,
        -u db-user: db_user is the database username.
        -h serverIP: ServerIP is the IP of your mysql server.
        -D db_name: db_name is the database name
        -p: password of the database user

The easiest way to transfer a Mysql database from one server to another is by creating a backup, copy the mysql database backup to another server and restore it on the remote server.

The first thing is to create a backup of the mysql database using mysqldump. It creates a backup in the .sql format. You have to use your SSH user/password while backup/restore of database.

To backup a specific database:

# mysqldump -u root -p db_name > db_name.sql

To backup a specific Mysql table:

# mysqldump -u root -p db_name tb_name > tb_name.sql

Copy the database/table backups to the remote server using SCP (ssh):

# scp db_name.sql root@remote_server_ip:/backup/
# scp tb_name.sql root@remote_server_ip:/backup/

Login to the remote server and restore the .sql file. You should be in the backup directory while restoring

# mysql -u root -p db_name < db_name.sql
# mysql -u root -p db_name < tb_name.sql

On a cPanel server, the mysql user is ‘root’ and the mysql password.
On a Plesk server the mysql user is ‘admin’ and the Plesk admin password.

In case you remove a lot of data from the tables OR change the database structure, a de-fragmentation/optimizing of the database is necessary to avoid performance loss, especially while running queries. To avoid performance loss, optimize the database.

SSH to your server and execute:

mysqlcheck -o <databasename>

where, -o stands for optimize which is similar to defragmentation. You should look to defragment the tables regularly when using VARCHAR fields since these columns get fragmented too often.