Difference between revisions of "MySQL"

From HE FAQ
Jump to: navigation, search
(→‎How do I access my database?: removed phpmyadmin)
m (Kcochran moved page mySQL to MySQL)
 
(9 intermediate revisions by 2 users not shown)
Line 2: Line 2:
  
 
== Can I use MySQL on your server? ==
 
== Can I use MySQL on your server? ==
 +
All new accounts come with a mysql database.
  
−
Yes, if you have a "Starter Virtual Host" or above.  Please note that MySQL is not enabled for "Simple Virtual Host" accounts.  A list of features for each account type is available [http://he.net/web_hosting.html here].  If you need this feature, please email support@he.net to request an upgrade of account type.
+
The database name is the same as your account name, and each account is allowed only one database per account.
  
 +
You can create as many tables as you need within the database and you may use table name prefixes if needed.
  
 
== How do I access my database? ==
 
== How do I access my database? ==
  
−
Your Account Database is already initialized by default and ready for your use.
+
The MySQL database for your account is automatically created at the time of account creation.
  
−
There are many MySQL-compatible database programs available that you can use.  Enter your server's name as the server or host name.  Your user name and database name are both the same as your HE account name, and your MySQL password as the password (which should be different from your admin/shell password).
+
Your mysql user name and mysql database name are both the same as your HE account name.
  
−
You can also manage your database via SSH.  After logging in with ssh, type: mysql -p .  It will prompt you for the database password.
+
Your MySQL password should be different from your admin/shell password.
  
−
You can also access it with the following commands:
+
When configuring scripts within your account that need access to the database, make sure to set localhost as the host name.
  
−
      mysql -u my_user_name --password=my_password
+
You can access and manage your database via SSH if needed.
−
      mysql -u my_user_name -p
+
  
−
Again, your user name and database name are both the same as your HE account name.
+
After logging in via SSH, you can access mysql with the following command:
 +
 
 +
      mysql -p username
 +
 
 +
It will then prompt you for your MySQL password.
  
 
== How do I change my mySQL password? ==
 
== How do I change my mySQL password? ==
Line 38: Line 43:
 
== What is the mysql port number? ==
 
== 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.
+
Port 3306 is the default MySQL port.
−
 
+
  
 
== How do I backup or transfer my database? ==
 
== 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 backing up and restore your database using SSH, use the mysqldump command:
  
−
To back up your database using PHPMyAdmin, log in, select your database, and use the "Export" tab.  Keep all the tables and the "SQL" format selected and click on "Go" in the lower right.  To restore the backup, use the "Import" tab.
+
      mysqldump -p user_name > db.sql
  
−
Backing up and restoring your database using SSH is a bit more complicated.  Once logged in, use the mysqldump command:
+
('db.sql' is the name of your backup dump file, which you can change to whatever name you choose, and user_name is your HE account name)
−
 
+
−
      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:
 
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:
Line 61: Line 61:
 
To restore the dump, just type:
 
To restore the dump, just type:
  
−
       mysql -p dbname < db.sql
+
       mysql -p user_name < db.sql
  
 
Note that the structure dump won't work if tables already exist. The backup will not change or replace the structure.
 
Note that the structure dump won't work if tables already exist. The backup will not change or replace the structure.
Line 69: Line 69:
 
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.
 
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.
+
To transfer a database, create the dump as described above, use SFTP, SCP or rsync 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
+
  
 
[[Category:Webhosting]]
 
[[Category:Webhosting]]

Latest revision as of 10:22, 30 April 2015

This information only pertains to Hurricane Electric's Shared Web Hosting package. There may be different information in our other categories.

Can I use MySQL on your server?

All new accounts come with a mysql database.

The database name is the same as your account name, and each account is allowed only one database per account.

You can create as many tables as you need within the database and you may use table name prefixes if needed.

How do I access my database?

The MySQL database for your account is automatically created at the time of account creation.

Your mysql user name and mysql database name are both the same as your HE account name.

Your MySQL password should be different from your admin/shell password.

When configuring scripts within your account that need access to the database, make sure to set localhost as the host name.

You can access and manage your database via SSH if needed.

After logging in via SSH, you can access mysql with the following command: 

     mysql -p username

It will then prompt you for your MySQL password.

How do I change my mySQL password?

You can set your MySQL password in http://admin.he.net. Log into your account and click on "Issue New Password". Re-enter your admin password in the "Password" field (unless you want to change it, too), and enter your new MySQL password in the "MySQL Password" field.

Alternatively, you can change your password from within MySQL (if you know your current one). 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?

Port 3306 is the default MySQL port.

How do I backup or transfer my database?

To backing up and restore your database using SSH, use the mysqldump command: 

     mysqldump -p user_name > db.sql

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

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 user_name < 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 SFTP, SCP or rsync to upload it on the second server, and then restore it as above.

Retrieved from "https://faq.he.net/index.php?title=MySQL&oldid=664"