MySQL — databases creation and management

Contents:

1. General information
2. Connection to database server
3. Database creation
4. Work with PHPMyAdmin

5. Work with database from command line

1. General information

• Servers with MySQL version 5.1.х are used on R01 hosting.
• Databases and users management is carried out in section "DBMS" of the hosting control panel (https://www.r01.ru, "Account manager").
• Operation with databases is possible from the scripts placed on hosting using PHPMyAdmin interface (if envisaged by the pricing schedule) and from the command line (by connection to hosting through SSH).
• Direct access to database servers from the Internet is closed for the security reasons, connection is possible only via ssh tunnel.
• By default database code is windows-1251.
• It is possible to work with tables MyISAM and InnoDB.

2. Connection to database server

The information for connection to database server can be viewed in section "DBMS" — "Users" of the control panel.

• Database server address (host): identifier.mysql is indicated in the top part of the page. "Identifier" is a unique name of the hosting service that is specified in the top part of the control panel next to the agreement number.
• Name of MySQL user (login): identifier_mysql — also specified on the page.
• To obtain MySQL user's password you need to check it and click on "Change password" button. New password will be displayed on the screen. To send password to your contact email address, check "Specify password in letter".
• When hosting is created database identifier_db will be created.

Note that in case of password change of MySQL user, websites which are placed on hosting and employ this MySQL user cannot operate properly, until password in scripts is changed to a new one.

3. Database creation

For each website we recommend creating a separate database. If envisaged by the selected plan, we recommend creating separate database users.

To create a new database, go to section "DBMS" — "Databases" of the control panel.

• In "New database" block enter name of database, for example test, and click on "Create".

  

• Then click on the name of the newly created database and set "Access rights" in value "Reading, writing, tables management" just opposite the selected website of the MySQL user.

  

Database created. You may use it for placement of a new website.

4. Work with PHPMyAdmin

PHPMyAdmin application allows working with databases through the browser.
It helps edit database contents, perform SQL requests, provides backup copying and restoration of databases.

To access PHPMyAdmin go to section "DBMS" — "PHPMyAdmin" of the control panel (https://www.r01.ru, section "Account manager").

• Use MySQL user's data for authorization (see item 2).

  

4.1. Creation of database backup copy (dump export)

To create backup copy, go to PHPMyAdmin interface.

• On the left part of the page select the required database.
• Go to "Export" tab.

  

• If necessary, change export settings and click "Ок".

  

The file with database contents will be downloaded to your computer.

To work with large volume databases we recommend using connection from the command line.

4.2. Restoration of database from backup copy (dump import)

We recommend creating new database (see item 3.) and importing dump to it.
To restore database go to PHPMyAdmin interface.

• In the left column select new database.
• Open "Import" tab (on the right top part).
• Click on "Select file" and indicate dump file saved on your computer.
• Set file coding (it usually matches website coding).
• If necessary, change export settings and click "Ок", wait till import is complete.

  

After database is imported, change in configuration files the name of the database and check operation.

To work with large volume databases we recommend using connection from the command line.

4.3. Changing database code by default

To change default database coding, for example to UTF-8, go to PHPMyAdmin interface.

• In the left column select the appropriate database and go to "Operations" tab (on the right top part).
• In "Comparison" block select "utf8_general_ci" and click "ОК".

  

5. Work with database from command line

Connection to hosting via SSH enables remote operation with the databases, with no need to download dumps to your computer. Operation with database dumps with a size more than 64 MB is possible only from the command line.

• Access to virtual server through SSH
• SSH — access and program setup

5.1. Creation and restoration of database dump from the command line

To create database dump run the following command:

mysqldump -y -f -q --default-character-set=binary --create-options --single-transaction --skip-extended-insert --add-drop-table -h dbhost -u dbuser -pdbpassword dbname > dump.sql

where:

dbhost — database server address,
dbuser — name of MySQL user,
dbpassword — password of MySQL user (written solidly with -p setting),
dbname — name of database,
dump.sql — name of database dump. Dump will be created in the current directory.

For dump import into database run the following command

mysql -h dbhost -u dbuser -pdbpassword dbname < dump.sql

We recommend first to create new database (see item 3.) and import dump to it.
Database dump shall be loaded to hosting.

• FTP —files placement, files upload to the server

After database is imported, change in configuration files the name of the database and check operation.