MySQL FAQs

[Arthur]

Hi

How can I access MySQL database through Telnet ?Fire1

[Michelle Taylor]

Once you sign up a web hosting account, your account database will be initialized by default and ready for use.

According to my knowledge you can access your database via Telnet by using following commands.

Type: mysql -p (It will prompt you for the mysql database )

Even you can access it by using following commands lines.

mysql -u my_user_name --password=my_password
mysql -u my_user_name -p

Your user name will be the same as your account name.

[Arthur]

How can I change MySQL database password via shell prompt?

[Michelle Taylor]

At first you need to log in to your MySQL database from a shell prompt. Then use the following commands:

set password=password('new_password');

where new_password will be the new password that you have selected.

For an example.

set password=password('pa$$w0rd');

[Arthur]

What is a MySQL default port number?

[Michelle Taylor]

MySQL port number is the default standard port that is 3306. Most of the leading programs like PHP and Perl DBI will take that default port number if it is not specified.

[Arthur]

How can I backup or transfer MySQL database?

[Michelle Taylor]

While taking backup of MySQL, one thing you need to understand that MySQL database are not backed up like ordinary files and folders. Even if it is possible, it would not be a standard solution, because it can corrupt the database file by doing so.

To back up your mysql database use ssh from your account, then use mysqldump commands :

mysqldump -ppassword database name > db1.sql (db1.sql is your backup dump file, you can use any namefor your dump file)

That backs up the entire database in standard sql text format. Both the structure (like CREATE table command) and data (like INSERT command) statements, can be backed up individually, which is useful in many cases.

Check: MySQL :: MySQL 5.1 Reference Manual :: 4.5.4 mysqldump ? A Database Backup Program, For all the command line options.

To restore the dump file, just type: mysql -p dbname < db1.sql

Take a note : That the structure of dump file will not work if tables already exist. The backup will not change or replace the structure. For the data dump it will add any new data if it doesn't exist already.

To transfer a mysql database, create the dump file as mentioned above, use FTP to upload it on the another server, and then restore it as above. Mostly people think that database backup can be done via FTP as well. But it is not correct, at first you want to generate mysql dump, and then need to download that dump file.

You can use following mysqldump command lines:

mysqldump -ppassword username > db1.sql

Use the next command to view the dump file:

view db1.sql
(exit with: q!)

To place the file back: upload the file.


You can edit it with the help of a text editor. Include the following line at the top and use the database name, which is similar to your user name. Next >> save the file and fire the command:

mysql -ppasswd < db1.sql (All the database will be generated, including structure as well)

For more information you need use MySQL :: MySQL 5.1 Reference Manual :: 4.5.4 mysqldump ? A Database Backup Program