"The mind of a Mad Tech!"

The easiest way to backup your database would be to telnet to the your database server machine and use the mysqldump command to dump your whole database to a backup file. If you do not have telnet or shell access to your server, don’t worry about it; I shall outline a method of doing so using the PHPMyAdmin web interface, which you can setup on any web server which executes PHP scripts.

Mysqldump

If you have either a shell or telnet access to your database server, you can backup the database using mysqldump. By default, the output of the command will dump the contents of the database in SQL statements to your console. This output can then be piped or redirected to any location you want. If you plan to backup your database, you can pipe the output to a sql file, which will contain the SQL statements to recreate and populate the database tables when you wish to restore your database. There are more adventurous ways to use the output of mysqldump.

A Simple Database Backup:

You can use mysqldump to create a simple backup of your database using the following syntax.

mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]

o [username] – this is your database username
o [password] – this is the password for your database
o [databasename] – the name of your database
o [backupfile.sql] – the file to which the backup should be written.

The dump file will contain all the SQL statements needed to create the table and populate the table in a new database server. To backup your database ‘Customers’ with the username ‘sadmin’ and password ‘pass21′ to a file custback.sql, you would issue the command:

mysqldump -u sadmin -p pass21 Customers > custback.sql

You can also ask mysqldump to add a drop table command before every create command by using the option –add-drop-table. This option is useful if you would like to create a backup file which can rewrite an existing database without having to delete the older database manually first.

mysqldump –add-drop-table -u sadmin -p pass21 Customers > custback.sql

*Note* – you don’t have to specify the password in the syntax. If you choose not to, it will simply ask for it once you implement the command.

Importing dumped file.

Now that you have the dump file, you’ll need to upload it to the server you need to restore the database to. Once you’ve done this you can connect to the server via SSH (linux) or RDC (windows). To import the file you can either browse to the directory first or you can specify the directory in the import syntax;

Now import the dump file into MySQL by typing all the following on 1 single line at the shell prompt:

mysql -p -h DBSERVER Customers < custback.sql

Basically you are telling Mysql to import custback.sql to the database Customers on the localhost.

The above assumes that your database name on our system is “Customers ” and the dumpfile that you uploaded was named “custback.sql”. Replace those with your correct database name and dumpfile filename. Also replace DBSERVER with your correct database server name.

MySQL max_allowed_packet

In which case, try adding one to your my.cnf file;

set-variable=max_allowed_packet=xM
Change ‘x’ to 1 (or increase it if you’re still getting errors)

If you are using the mysql client program, its default max_allowed_packet variable is 16MB. That is also the maximum value before MySQL 4.0. To set a larger value from 4.0 on, start mysql like this:

mysql> mysql –max_allowed_packet=32M

That sets the packet size to 32MB.

The server’s default max_allowed_packet value is 1MB. You can increase this if the server needs to handle big queries (for example, if you are working with big BLOB columns). For example, to set the variable to 16MB, start the server like this:

mysql> mysqld –max_allowed_packet=16M

Before MySQL 4.0, use this syntax instead:

mysql> mysqld –set-variable=max_allowed_packet=16M

You can also use an option file to set max_allowed_packet. For example, to set the size for the server to 16MB, add the following lines in an option file:

[mysqld]
max_allowed_packet=16M

Before MySQL 4.0, use this syntax instead:

[mysqld]
set-variable = max_allowed_packet=16M

It’s safe to increase the value of this variable because the extra memory is allocated only when needed. For example, mysqld allocates more memory only when you issue a long query or when mysqld must return a large result row. The small default value of the variable is a precaution to catch incorrect packets between the client and server and also to ensure that you don’t run out of memory by using large packets accidentally.

You can also get strange problems with large packets if you are using large BLOB values but have not given mysqld access to enough memory to handle the query. If you suspect this is the case, try adding ulimit -d 256000 to the beginning of the mysqld_safe script and restarting mysqld.

The max_allowed_packet option specifies how large your MySQL Query/Data Packet can be, and NOT the Maximum File Size that MySQL can maintain. MySQL can theoretically handle database files upto 1 Terrabyte in size.

If mysql service is failing upon start, you will have to place the following code in /etc/my.cnf under ‘mysqld’ section and restart mysql service.

“skip-innodb” (Do not add the quotes).

I recently ran into an issue where I would update the Cpanel instance using the:

/scripts/upcp –force

To update Cpanel, as I normally do. However I noticed that when I did it this time, It did not update MySQL. Thinking that’s fine, I’ll just check the MySQL 5.0 button in Main >> Server Configuration >> Tweak Settings. However the option to update to version 5.0 was not there…. This was puzzling indeed.

I then ran /scripts/mysqlup –force . That didn’t help either, version 4 was still there and no sign of version 5. I then tried to see what rpm’s where in place;

rpm -qa | grep My

Nothing but MySQL 4……. Still at a complete loss.

Then I finally looked at the config file for cpanel;

/var/cpanel/cpanel.config

Look at that:

maxemailsperhour=0
maxmem=128
myname=cpaneld
mysql-version=5.1
mysqldebug=0
nativessl=1
nobodyspam=0
nouserbackupwarn=0

MySQL 5.1????????? Changing this to 5.0 and then running the /scripts/mysqlup --force
command fixed this issue. I now can select MySQL 5.0 by going to  Main >> Server Configuration >>
Tweak Settings.

Looks like the new update of Cpanel has borked the MySQL install.

Plesk does support MySql 5, however only with Plesk 8.1 and beyond. If you are using an earlier version of Plesk, you will be able to upgrade to MySql 5, however it will and has broken Plesk to the point where it is unusable.

“Plesk Control Panel fully supports MySQL 5 since Plesk 8.1 version. It means that there are no point in Plesk code which conflicts with MySQL 5 and if you replace mysql4 with MySQL 5, it does not affect Plesk itself, all the SQL requests Plesk sends to MySQL from any Plesk’s place will be processed without problems. There are no dependencies which do not allow mysql5 be installed.

If you are running Plesk version below than 8.1, you should upgrade to Plesk 8.1 first, then upgrade Mysql. Otherwise, if you upgrade MySQL to the 5th version on the server running Plesk 8.0.x or older, Plesk functioning and upgrade to further versions problems are possible.

Mysql 5 should be taken from the OS distribution and installed by means of the OS package manage in order to avoid possible dependencies conflicts.”