Taking dump of multiple tables
The mysqldump can be used to dump a database or a collection of databases for backup or for transferring the data to another MySQL server.
Execute the following command to get the dump of a table.
$ mysqldump -uusername -p databasename tablename > dump_filename.sql
This table can be added to the database using the following command.
$ mysql -uusername -p databasename < dump_filename.sql
In order to take dump of N tables named table1, table2.table3….tableN use the following syntax:
$ mysqldump -uusername -p databasename table1 table2 table3 …. tableN > dump_filename.sql
Upgrade MySQL system tables
When you try to create a remote MySQL user, sometimes you may get the following the error.
ERROR 1146 (42S02): Table ‘mysql.procs_priv’ doesn’t exist
Why this error occurs?
MySQL system tables should be updated while upgrading MySQL version in the server and make sure that their structure is up to date. Otherwise, this error will occur when you create a remote user.
You can eliminate this error by executing the command in command prompt.
mysql_fix_privilege_tables –password=root_password
This command will update MySQL tables and its structure.
Note: Before executing this command, make sure that you have taken the full backup of MySQL.
MySQL: Access denied for user
MySQL Error : Error connecting to MySQL: Access denied for user: ‘root@localhost’ (Using password: YES)
This is mainly caused due to the fact that the user root does not have enough privileges to access the mysql databases or the password set for the user root to connect mysql was changed.
1. Start mysql using mysqld_safe
#/usr/local/etc/rc.d/mysqld stop
#mysqld_safe –skip-grant-tables &
Note:
mysqld_safe is used to start mysql server by disabling certain feature that restrict a user to access mysql. The option –skip-grant-tables is used to neglect the permission grant to different users of mysql.
2. Enter mysql prompt by just typing “mysql” and do the following
>GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY ‘newpassword’ WITH GRANT OPTION;
>FLUSH PRIVILEGES;
>\q
3. Kill all mysql processes and start mysqld
#killall -9 mysqld_safe
#killall -9 mysqld
#/usr/local/etc/rc.d/mysqld start
Method to take mysql dump of table structures
Inorder to take the dump of a database table structure you can use the option “-d” of mysqldump.
The syntax is given below.
$ mysqldump -u USERNAME -p -d DATABASENAME –tables TABLENAME1 TABLENAME2 > FILENAME.sql
Replace the USERNAME, DATABASENAME, TABLENAME, FILENAME with the original one.
You can verify the file for the table structures.
To Convert from vm-pop3d to Dovecot on Directadmin
To convert to dovecot from wu-imap or vm-pop3d, you can follow the steps given below
The “wu-imap” and “vm-pop3d” are in “mbox” format. The “Dovecot” is using the “Maildir” format.
cd /usr/local/directadmin/customapache
./build update
./build update_dovecot
./build dovecot
./build todovecot
Make sure that you do not stop the “todovecot” command as it can break the whole email system. It can take a long time to convert everything but do not stop it half way through.
Also, make sure that everything is working fine after the conversion.
You can clean the old “mbox” files by typing:
echo “action=delete&value=mbox” >> /usr/local/directadmin/data/task.queue
