Difference between revisions of "MySQL"

From HE FAQ
Jump to: navigation, search
(Added questions from he.net/faq per mleber)
Line 83: Line 83:
  
 
       mysqldump --help
 
       mysqldump --help
 +
 +
**********
 +
 +
How do I access My database?
 +
 +
Your Account Database is already initialized by default and ready for your use.
 +
 +
After logging in with ssh, type: mysql -p
 +
It will prompt you for the database password.
 +
 +
You can also access it with the following commands:
 +
 +
      mysql -u my_user_name --password=my_password
 +
      mysql -u my_user_name -p
 +
 +
Your User Name and Database Name are both the same as your HE account name.
 +
How do I change my mySQL password?
 +
 +
First, log in to your mySQL database from a shell prompt. Then issue the following command:
 +
 +
      set password=password('new_password');
 +
 +
where new_password is the new password you have selected.
 +
 +
Example
 +
 +
      set password=password('charlie1');
 +
 +
What is the mysql port number?
 +
 +
The port is the default standard port: 3306. Most programs like PHP and Perl DBI will take this port if it is not specified.
 +
How do I backup or transfer my database?
 +
 +
mySQL databases are not backed up like ordinary files. Even if it were possible, it would not be a good solution, as it could corrupt the database by doing so.
 +
 +
To back up your database, ssh to your account, then use the mysqldump command:
 +
 +
      mysqldump -ppassword DatabaseName > db.sql
 +
 +
('db.sql' is the name of your backup dump file, which you can change to whatever name you choose)
 +
 +
That backs up the whole database in standard SQL text format. Both the structure (like CREATE table command) and data (like INSERT command) statements, can be backed up separately, which is usefull in many cases. Check:
 +
 +
mysqldump --help
 +
 +
for all the command line options.
 +
 +
To restore the dump, just type:
 +
 +
      mysql -p dbname < db.sql
 +
 +
Note that the structure dump won't work if tables already exist. The backup will not change or replace the structure.
 +
 +
For the data dump (the INSERT commands) it will append any new data if it does not yet exist.
 +
 +
If you have a database that is in frequent use, such as one linked to a dynamic web interface being updated by your web site visitors, it is a good idea to make a daily backup of the data.
 +
 +
To transfer a database, create the dump as described above, use ftp to upload it on the second server, and then restore it as above.
 +
 +
Backing up the database is not done through an ftp database directly. You first need to generate an sql dump, and then download this dump.
 +
 +
Use the following mysqldump command:
 +
 +
      mysqldump -ppassword username > somefile.sql
 +
 +
Then use the following command to view the dump:
 +
 +
      view somefile.sql
 +
 +
(exit with: q!)
 +
 +
To put the file back:
 +
 +
    * upload the file
 +
    * edit it with a text editor, adding the below line to the top:
 +
            use databasename (which is the same as your username)
 +
    * save the file and issue the command:
 +
            mysql -ppasswd < somefile.sql
 +
 +
All of the database will be generated, including structure. For more details, use
 +
 +
      mysqldump --help
 +
 +
How is 'mySQL' pronounced?
 +
 +
      my-es-queue-el
  
 
[[Category:Webhosting]]
 
[[Category:Webhosting]]

Revision as of 22:41, 17 May 2010

How do I access my database?

Your Account Database is already initialized by default and ready for your use.

After logging in with ssh, type: mysql -p It will prompt you for the database password.

You can also access it with the following commands: 

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

Your User Name and Database Name are both the same as your HE account name.


How do I change my mySQL password?

First, log in to your mySQL database from a shell prompt. Then issue the following command: 

     set password=password('new_password');

where new_password is the new password you have selected.

Example 

     set password=password('charlie1');


What is the mysql port number?

The port is the default standard port: 3306. Most programs like PHP and Perl DBI will take this port if it is not specified.


How do I backup or transfer my database?

mySQL databases are not backed up like ordinary files. Even if it were possible, it would not be a good solution, as it could corrupt the database by doing so.

To back up your database, ssh to your account, then use the mysqldump command: 

     mysqldump -ppassword DatabaseName > db.sql

('db.sql' is the name of your backup dump file, which you can change to whatever name you choose)

That backs up the whole database in standard SQL text format. Both the structure (like CREATE table command) and data (like INSERT command) statements, can be backed up separately, which is usefull in many cases. Check: 

     mysqldump --help

for all the command line options.

To restore the dump, just type: 

     mysql -p dbname < db.sql

Note that the structure dump won't work if tables already exist. The backup will not change or replace the structure.

For the data dump (the INSERT commands) it will append any new data if it does not yet exist.

If you have a database that is in frequent use, such as one linked to a dynamic web interface being updated by your web site visitors, it is a good idea to make a daily backup of the data.

To transfer a database, create the dump as described above, use ftp to upload it on the second server, and then restore it as above.

Backing up the database is not done through an ftp database directly. You first need to generate an sql dump, and then download this dump.

Use the following mysqldump command: 

     mysqldump -ppassword username > somefile.sql

Then use the following command to view the dump: 

     view somefile.sql

(exit with: q!)

To put the file back:

  • upload the file
  • edit it with a text editor, adding the below line to the top: 
     use databasename (which is the same as your username)
  • save the file and issue the command: 
     mysql -ppasswd < somefile.sql

All of the database will be generated, including structure. For more details, use 

     mysqldump --help

How do I access My database?

Your Account Database is already initialized by default and ready for your use.

After logging in with ssh, type: mysql -p It will prompt you for the database password.

You can also access it with the following commands: 

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

Your User Name and Database Name are both the same as your HE account name. How do I change my mySQL password?

First, log in to your mySQL database from a shell prompt. Then issue the following command: 

     set password=password('new_password');

where new_password is the new password you have selected.

Example 

     set password=password('charlie1');

What is the mysql port number?

The port is the default standard port: 3306. Most programs like PHP and Perl DBI will take this port if it is not specified. How do I backup or transfer my database?

mySQL databases are not backed up like ordinary files. Even if it were possible, it would not be a good solution, as it could corrupt the database by doing so.

To back up your database, ssh to your account, then use the mysqldump command: 

     mysqldump -ppassword DatabaseName > db.sql

('db.sql' is the name of your backup dump file, which you can change to whatever name you choose)

That backs up the whole database in standard SQL text format. Both the structure (like CREATE table command) and data (like INSERT command) statements, can be backed up separately, which is usefull in many cases. Check:

mysqldump --help

for all the command line options.

To restore the dump, just type: 

     mysql -p dbname < db.sql

Note that the structure dump won't work if tables already exist. The backup will not change or replace the structure.

For the data dump (the INSERT commands) it will append any new data if it does not yet exist.

If you have a database that is in frequent use, such as one linked to a dynamic web interface being updated by your web site visitors, it is a good idea to make a daily backup of the data.

To transfer a database, create the dump as described above, use ftp to upload it on the second server, and then restore it as above.

Backing up the database is not done through an ftp database directly. You first need to generate an sql dump, and then download this dump.

Use the following mysqldump command: 

     mysqldump -ppassword username > somefile.sql

Then use the following command to view the dump: 

     view somefile.sql

(exit with: q!)

To put the file back: 

   * upload the file
   * edit it with a text editor, adding the below line to the top:
           use databasename (which is the same as your username)
   * save the file and issue the command:
           mysql -ppasswd < somefile.sql

All of the database will be generated, including structure. For more details, use 

     mysqldump --help

How is 'mySQL' pronounced? 

     my-es-queue-el
Retrieved from "https://faq.he.net/index.php?title=MySQL&oldid=218"