- Print
- PDF
Backup
- Print
- PDF
The latest service changes have not yet been reflected in this content. We will update the content as soon as possible. Please refer to the Korean version for information on the latest updates.
Available in Classic and VPC
In Backup, you can check the backup information configured per server for safe storage of cache data for users using Cloud DB for MySQL. Also, when the cache data is damaged due to a failure, you can perform restoration using the backup file that was in storage. In addition to servers using high availability settings, you can also use the backup and restoration features for standalone servers. However, point-in-time restoration is not supported for standalone servers.
To use the backup and restoration, it is recommended to first understand the basic execution rules for the backup provided by Cloud DB for MySQL. The basic backup execution rules are as follows:
- Backup execution methods
- Performed daily, once a day
- Select between automatic settings and user-defined settings
- Automatic settings: a random time will be specified when an MySQL server is created, and the backup will be performed at a time similar to the time of the first backup.
- User-defined settings: backups are performed within 15 minutes from the time selected by the user
- Exceptional situations
- Backup is performed within 30 minutes from the DB creation
- When DB settings change, the backup is performed up to 5 minutes after the user-defined backup time
- Backup file
- Retention period: can be stored up to 30 days according to the user settings
- Storage location: a separate data storage (Storage contracts are made according to the backup file size)
Backup page
The basics of using the backup are as follows:
Field | Description |
---|---|
① Menu name | Name of the menu currently being viewed |
② Basic features | Check details for Cloud DB for MySQL, refresh the Backup page |
③ Backup list | Configured backup settings per server and settings information |
Check backup settings list
You can check the backup settings list per MySQL server in operation. The following describes how to check the backup settings list.
- From NAVER Cloud Platform console, click the Services > Database > Cloud DB for MySQL menus.
- Click the Backup menu.
- When the backup settings list appears, check the required information.
- DB service name: DB service name specified by user
- Backup retention period: maximum number of days for the backup file to be stored in data storage
- Backup start time: time at which the backup is performed once a day
- Backup data size: size of the completed backup file
- Latest backup date: date for the latest backup that was performed
- View details: details and restoration of the backup file list created per server, saved to Object Storage
Check backup file list per server
The following describes how to check the backup file list created for each server after completing the backup:
- From NAVER Cloud Platform console, click the Services > Database > Cloud DB for MySQL menus.
- Click the Backup menu.
- Click the [Details] button from View details of the backup settings row you want to check the details for.
- Check the details for the backup file.
- Backup date: date that the backup was performed
- Backup start time: time when the backup was started
- Backup completion time: time when the backup was completed
- Elapsed time: time it took for the backup to be completed
- Backup size: size of the created backup file upon completion of backup (MB)
- Data storage: size of the MySQL's data storage
Restore data from console
Cloud DB for MySQL provides a service that enables you to restore lost data using backup files, and easily and quickly create an MySQL server. Also, you can restore the data up to the desired time within the available range of restoration using the point-in-time restoration feature.
Backup file restoration
You can restore data using the backup file created through automatic backup.
The following describes how to restore data using the created backup file.
- From NAVER Cloud Platform console, click the Services > Database > Cloud DB for MySQL menus.
- Click the Backup menu.
- Click the [Details] button of the backup settings row you want to restore.
- Select the backup file and click the [Restore backup file] button.
- When the Restore backup file pop-up window appears, check or enter the required information for restoration.
- Backup time: range of time available for restoration (up to 7 days)
- Backup completion time: the point in time to restore to
- Private sub domain (VPC environment): the private sub domain to be used on the server to be restored (can't be selected)
- DB server type: MySQL server type to be restored
- DB server name: the name of the MySQL server to be restored
- Create as a new DB service: create the server to be restored as a new service
- DB service name: new DB service name
- High availability: when creating a new service, create it as a high availability configuration
- Multi-zone: create the Standby Master Server and Master Server in different availability zones
- Subnet: the master subnet and the subnet of another availability zone
- Click the [Restore] button or the [Create] button.
- Click the [OK] button.
- Click DB server.
- Check the status of the server being restored.
- Created as Recovery type
- Creating: status in which the MySQL server is being created (restored) with the information entered by the user
- Configuring: status in which the MySQL server is being configured after being created (restored) with the information entered by the user
- Running: status in which the creation (restoration) and configuration of the MySQL server has been completed with the information entered by user, and access to MySQL from the application server is available.
The completion of the server restoration may take several minutes.
Point-in-time restoration
You can restore data to a desired point in time. You can restore up to the desired minute within the range available for restoration.
The following describes how to restore data using the point-in-time recovery feature.
However, point-in-time restoration is not supported for standalone type servers.
- From NAVER Cloud Platform console, click the Services > Database > Cloud DB for MySQL menus.
- Click the Backup menu.
- Click the [Details] button of the backup settings row you want to restore.
- Click the [Point-in-time restoration] button.
- When the Point-in-time recovery pop-up window appears, check or enter the required information for restoration.
- Available DB restoration time: range of time available for restoration (up to 7 days)
- DB restoration time: the point in time to restore to
- Private sub domain (VPC environment): the private sub domain to be used on the server to be restored (cannot select)
- DB server type: MySQL server type to be restored
- DB server name: the name of the MySQL server to be restored
- Click the [Restore] button or the [Create] button.
- Click the [OK] button.
- Click DB server.
- Check the status of the server being restored.
- Created as Recovery type
- Creating: status in which the MySQL server is being created (restored) with the information entered by the user
- Configuring: status in which the MySQL server is being configured after being created (restored) with the information entered by the user
- Running: status in which the creation (restoration) and configuration of the MySQL server has been completed with the information entered by user, and access to MySQL from the application server is available.
The completion of the server restoration may take several minutes.
Create a new DB service using restored server
You can create a new DB service based on the recovery server created through the restoration feature, and then change the server to a standalone or high availability configuration server. The specifications, DB settings, and backup times for the server are retained. When restoring a server whose backup time is not specifically set, the retention period is set as one day.
The recovery server with encrypted data storage can only create DB service of high availability configuration.
- From NAVER Cloud Platform console, click the Services > Database > Cloud DB for MySQL menus.
- Click DB server.
- Click the recovery server you want to create a new DB service from, and then click the [Manage DB] button.
- Click the [Create new DB service] button.
- When the Create new DB service pop-up window appears, select whether to support high availability, enter the DB service name, and then click the [Yes] button.
- Check the status of the server being created.
- Creating: status in which the MySQL server is being created with the information entered by the user
- Configuring: status in which the MySQL server is being configured after being created with the information entered by the user
- Running: status in which the creation and configuration of the MySQL server has been completed with the information entered by user, and access to MySQL from the application server is available
Store in Object Storage
In Cloud DB for MySQL, you can store the created backup file to Object Storage to use for backup. The following describes how to save a stored backup file to Object Storage:
Additional fees apply when you request a subscription to Object Storage. For the introduction to Object Storage and details about its pricing plans, see the Services > Storage> Object Storage menu in NAVER Cloud Platform portal.
In case of the MySQL service created in the Secure Zone, you need to add related policies first in the NAVER Cloud's Secure Zone. In the Create policy pop-up window, select MySQL server and Object Storage for the Source IP and Destination IP fields respectively. Please refer to Secure Zone for more information about the Secure Zone.
- From NAVER Cloud Platform console, click the Services > Database > Cloud DB for MySQL menus.
- Click the Backup menu.
- Click the [Details] button of the backup settings row for the file you want to save to Object Storage.
- Select the backup file and click the [Send to Object Storage] button.
- When the Send to Object Storage pop-up window appears, click and select the bucket and folder you want to save into.
- Press the [Send to Object Storage] button.
- Click the [OK] button.
- When sending to Object Storage, unlocking bucket, appropriate access control, and ACL settings are required.
- In case of the Japanese region, disable access control settings for the Object Storage bucket.
- Exporting to Object Storage may take several minutes to be completed.
Use backup utility
You can backup and restore the data on a MySQL server using utilities such as Percona XtraBackup and mysqldump.
Restore backup file using XtraBackup
After saving the backup file to NAVER Cloud Platform's Object Storage, you can use XtraBackup to restore data from the server.
The prerequisites for restoring a backup file saved to Object Storage using XtraBackup are as follows.
- The major version of MySQL installed on the user's separate server is the same as the major version of MySQL that was backed up
- The XtraBackup binary required for restoration of backup file exists
- At the MySQL server to be restored, my.cnf file with
datadir
variable exists - The OS user executing the restoration command is able to access and write in the
datadir
directory - MySQL is in the shutdown status in the server to be restored
- If the server is using another OS other than CentOS, then you need to download the XtraBackup binary for the OS image you are using from the download link.
- For descriptions on XtraBackup binary options, refer to Percona official document(English).
To restore data using XtraBackup, proceed with the following steps in order. The description is based on CentOS 7.8 application servers.
1. Prepare the backup file at the Object Storage
- Store the backup file of the MySQL server to restore to the Object Storage, referring to Save to Object Storage.
- From NAVER Cloud Platform console, click Services > Storage > Object Storage > Bucket Management menus in this order, and click the bucket you want to save the backup file to.
- Click the saved backup file, and then click the Edit > Manage permissions menus in order.
- Set the "Open to fall field" to Public, and click the [OK] button.
- From the details of the saved backup file, copy the link in the Link field.
2. Restore data using XtraBackup
Access the application server, referring to the Getting started with Cloud DB for MySQL.
- If the server is using MySQL 5.7 version, then add the
innodb_undo_tablespaces = 2
statement under the[mysqld]
statement of the my.cnf file.
- If the server is using MySQL 5.7 version, then add the
Paste the copied link in the command below, and then run it to download the backup file.
- Once downloading the backup file is completed, change the Public settings to Private from the "Manage permissions" of the Object Storage.
# wget [Link] -- Examples # wget https://kr.beta-object.ncloudstorage.com/mysql-b/20211013_BACKUP.1634094735
Run the commands shown below, in order, to download XtraBackup.
- MySQL 5.7 version
# cd ~ # wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.9/binary/tarball/percona-xtrabackup-2.4.9-Linux-x86_64.tar.gz # tar xzf percona-xtrabackup-2.4.9-Linux-x86_64.tar.gz # cd ./percona-xtrabackup-2.4.9-Linux-x86_64 # XTRABACKUP_DIR=`pwd`
- MySQL 8.0 version
# cd ~ # wget https://downloads.percona.com/downloads/Percona-XtraBackup-8.0/Percona-XtraBackup-8.0.23-16/binary/tarball/percona-xtrabackup-8.0.23-16-Linux-x86_64.glibc2.17.tar.gz # tar xzf percona-xtrabackup-8.0.23-16-Linux-x86_64.glibc2.17.tar.gz # cd ./percona-xtrabackup-8.0.23-16-Linux-x86_64.glibc2.17 # XTRABACKUP_DIR=`pwd`
Run the following command to set the absolute path of the my.cnf file.
# MYSQL_CONF=/etc/my.cnf
Run the following commands, in order, to create a temporary directory and copy the backup file.
# cd ~ # mkdir backup # cd ./backup/ # cp ~/[Backup file name]. -- Examples # cp ~/20211013_BACKUP.1634094735 .
Run the following commands, in order, to specify the path of
BACKUP_DIR
as the location of the temporary directory, and check if each path has been specified properly.# BACKUP_DIR=`pwd` # echo $XTRABACKUP_DIR # echo $MYSQL_CONF # echo $BACKUP_DIR
Run the following commands, in order, to end MySQL in operation and extract the file from
xbstream
.# systemctl stop mysqld # cd $BACKUP_DIR # cat [uploaded_ backupfile] | ${XTRABACKUP_DIR}/bin/xbstream -x -- Examples # cat 20211013_BACKUP.1634094735 | ${XTRABACKUP_DIR}/bin/xbstream -x
Run the following command to restore the backup file to the created temporary directory.
- MySQL 5.7 version
# ${XTRABACKUP_DIR}/bin/innobackupex --defaults-file=${MYSQL_CONF} --apply-log ${BACKUP_DIR}/
- MySQL 8.0 version
# ${XTRABACKUP_DIR}/bin/xtrabackup --defaults-file=${MYSQL_CONF} --prepare --target-dir=${BACKUP_DIR}/
Open the my.cnf file to check the location of datadir, and then run the following command to move the directory for the datadir variable to another temporary backup folder.
# mv [data directory location] /[new_directory location] -- Examples # mv /var/lib/mysql /new_directory
Run the following command to move the backup file to the newly created MySQL directory.
- MySQL 5.7 version
# ${XTRABACKUP_DIR}/bin/innobackupex --defaults-file=${MYSQL_CONF} --move-back ${BACKUP_DIR}
- MySQL 8.0 version
# ${XTRABACKUP_DIR}/bin/xtrabackup --defaults-file=${MYSQL_CONF} --move-back --target-dir=${BACKUP_DIR}/
Enter the following commands, in order, to move to the location of datadir, and then change the datadir permission to
mysql
.- Upon completion of the change, you can run the
ll
command to check if the permission has been changed properly.
# cd /var/lib/mysql # chown -R mysql:mysql [data directory path] -- Examples # chown -R mysql:mysql /var/lib/mysql
- Upon completion of the change, you can run the
Add 'skip-grant-tables' under the
[mysqld]
statement of the my.cnf file.Run the following commands, in order, to start MySQL and create a new root account.
- When running
create
, theERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'localhost'
error may occur. In this case, run thedrop user 'root'@'localhost';
command to delete the existing root account, and then runcreate
again.
# systemctl start mysqld mysql -u root -p mysql > flush privileges; mysql > create user root@localhost identified by 'password'; mysql > grant all privileges on *.* to root@localhost with grant option; mysql > flush privileges;
- When running
Delete the
skip-grant-tables
statement added under the[mysqld]
statement in the my.cnf file, and then run the following command to restart MySQL.# systemctl restart mysqld
DB backup and restoration using mysqldump
You can perform backup and restoration of MySQL server in the application server using the mysqldump utility.
DB backup using mysqldump
The following describes how to backup a DB in the server using the mysqldump utility.
- Since the Cloud DB for MySQL uses Global Transaction Identifier(GTID), the
--set-gtid-purged=OFF
option needs to be added during the backup to restore a MySQL DB that does not use GTID. - If you want to create a replication connection with the Cloud DB for MySQL immediately after performing backup, then delete the
--set-gtid-purged=OFF
option to enable the use of GTID. - In case of restoring the backup file of the Cloud DB for MySQL to another Cloud DB for MySQL service, activate the
--set-gtid-purged=OFF
option and proceed with the backup only for the user DB.
Access the application server, referring to the Getting started with Cloud DB for MySQL.
Run the following command to backup the desired DB.
You can check the user ID, password, private domain, and DB name from server details of each server in the DB Server menu in the console.- Backup only specific DBs
# mysqldump --set-gtid-purged=OFF -u [User_ID] -p -h [DB Private domain] -S [mysql.sock location] --single-transaction --databases [name of the database to backup] > [name of the backup file to create].sql -- Examples # mysqldump --set-gtid-purged=OFF -u person -p -h db-1qv7d.beta-cdb.ntruss.com -S /var/lib/mysql/mysql.sock --single-transaction --databases dbdb > dumpfile.sql
- Backup all DBs
--mysqldump 5.7 or earlier versions # mysqldump --set-gtid-purged=OFF -u [User_ID] -p -h [DB Private domain] -S [mysql.sock location] --single-transaction --all-databases > [name of the backup file to create].sql -- Examples # mysqldump --set-gtid-purged=OFF -u person -p -h db-1qv7d.beta-cdb.ntruss.com -S /var/lib/mysql/mysql.sock --single-transaction --all-databases > dumpfile.sql --mysqldump 8.0 version # mysqldump --set-gtid-purged=OFF -u [User_ID] -p -h [DB Private domain] -S [mysql.sock location] --single-transaction --column-statistics=0 --all-databases > [name of the backup file to create].sql -- Examples # mysqldump --set-gtid-purged=OFF -u person -p -h db-1qv7d.beta-cdb.ntruss.com -S /var/lib/mysql/mysql.sock --single-transaction --column-statistics=0 --all-databases > dumpfile.sql
Restore backup file using mysqldump
The following describes how to restore data from the server using the mysqldump utility.
- Access the application server, referring to the Getting started with Cloud DB for MySQL.
- Run the following command to restore the desired backup file.
# mysql -u root -p < [backup file name].sql
-- Examples
# mysql -u root -p < dumpfile.sql
--If the `ERROR 3546 (HY000)` error occurs when performing restoration from a backup file with the `--set-gtid-purged=OFF` option excluded, run the following commands in order, and then perform the restoration
# mysql -u root - p
mysql> reset master;
Replication connection
You can create a replication connection between the slave server and master server, and check the replication status.
The following describes how to create a replication connection.
The master server is a server that uses a private domain, and the slave server is a local server.
Create a user account to be used for the replication connection by referring to the Manage DB user.
- For HOST (IP), enter the Private IP of the application server where the slave server is located.
Add the following settings under the
[mysqld]
statement of the my.cnf file.- The
server-id
should be set with a unique value to identify each server in the replication configuration, and you can only enter a number between 1 and 4294967295.
# Replication server-id = 2 log-bin = mysql-bin relay-log = relay-bin report-host = ncp-mysql-server master_info_repository = FILE relay_log_info_repository = FILE slave_type_conversions = ALL_NON_LOSSY log_slave_updates # required! GTID read_only # required! # Replication GTID enforce-gtid-consistency # required! GTID gtid-mode = ON # required! GTID
- The
Run the following command to restart MySQL.
systemctl restart mysqld
Back up the DB by referring to the Use backup utility.
Run the following commands, in order, to access the slave server with the root account and change the master.
mysql -u root -p
mysql> CHANGE MASTER TO
MASTER_HOST = 'DB Private domain',
MASTER_USER = 'User_ID',
MASTER_PASSWORD = 'user password',
MASTER_PORT = 3306,
MASTER_AUTO_POSITION = 1;
-- Examples
mysql> CHANGE MASTER TO
-> MASTER_HOST = 'db-1qv7d.beta-cdb.ntruss.com',
-> MASTER_USER = 'replication',
-> MASTER_PASSWORD = 'password1!',
-> MASTER_PORT = 3306,
-> MASTER_AUTO_POSITION = 1;
- Run the following command to connect to the master server from the slave server.
mysql> START SLAVE;
Confirm connection
After completing the replication connection, run the following command and refer to the table to check the connection status.
mysql> show slave status \G;
Items | Description |
---|---|
Slave_IO_State | Current status of the slave server |
Master_Host | Host name or IP address of the master server that the slave server is connected to |
Master_User | User account used for the slave server's connection to the master server |
Master_Port | Port used for the slave server's connection to the master server |
Connect_Retry | Duration of the reconnection attempt when the connection is lost (default: 60 seconds)CHANGE MASTER TO Can be changed through the command |
Master_Log_File | Name of the master server's binary log file that the I/O thread is currently reading |
Read_Master_Log_Pos | Location of the master server's binary log file that the I/O thread is currently reading |
Relay_Log_File | Name of the slave server's relay log that the SQL thread is currently reading |
Relay_Log_Pos | Location of the slave server's relay log that the SQL thread is currently reading |
Relay_Master_Log_File | Name of the source binary log file that includes the recent events executed by the SQL thread |
Slave_IO_Running | I/O thread operation status of the slave server, displays the connection status to the master server through one of the three following values
|
Slave_SQL_Running | Displays if the SQL thread has started |
Replicate_Do_DB | DB list specified with the --replicate-do-db option |
Replicate_Ignore_DB | Database list specified with the --replicate-ignore-db option |
Replicate_Do_Table | Database list specified with the --replicate-do-table option |
Replicate_Ignore_Table | Database list specified with the --replicate-ignore-table option |
Replicate_Wild_Do_Table | Database list specified with the --replicate-wild-do-table option |
Replicate_Wild_Ignore_Table | Database list specified with the --replicate-wild-ignore-table option |
Last_Errno , Last_Error | Same value as the Last_SQL_Errno item, the value can be reset with the RESET MASTER and RESET SLAVE commands.(When the SQL thread of the slave server receives an error, it reports the error first before stopping the SQL thread. Thus, even if the value of Slave_SQL_Running is YES when the SHOW SLAVE STATUS command is run, the value of this field does not show) |
Skip_Counter | Displays the current value of SQL_Slave_Skip_Counter , which is a system variable |
Exec_Master_Log_Pos | This is the location of the current master's binary log file read and run by the SQL thread. It displays the start of the transaction or event to be processed next. (When connecting a new slave server, if you specify a value for the MASTER_LOG_POS option in the CHANGE MASTER TO command, then the connected slave server starts reading from the specified location.) |
Relay_Log_Space | Sum value of the size of all existing relay log files |
Until_Condition | Value specified in the START_SLAVE command, displayed as one of the following values
|
Until_Log_File | Name of the log file that the SQL thread will run, and then suspend |
Until_Log_Pos | Position value of the log file that the SQL thread will run, and then suspend |
Master_SSL_Allowed | The SSL parameter used for the slave server's connection to the master server, displayed as one of the following values
|
Master_SSL_CA_File | SSL parameter used for the slave server's connection to the master server |
Master_SSL_CA_Path | SSL parameter used for the slave server's connection to the master server |
Master_SSL_Cert | SSL parameter used for the slave server's connection to the master server |
Master_SSL_Cipher | SSL parameter used for the slave server's connection to the master server |
Master_SSL_Key | SSL parameter used for the slave server's connection to the master server |
Seconds_Behind_Master | Displays how much the replication speed of the slave server has slowed down |
Master_SSL_Verify_Server_Cert | SSL parameter used for the slave server's connection to the master server |
Last_IO_Errno | Number of the error that stopped the I/O thread most recently |
Last_IO_Error | Message of the error that stopped the I/O thread most recently |
Last_SQL_Errno | Number of the error that stopped the SQL thread most recently |
Last_SQL_Error | Message of the error that stopped the SQL thread most recently |
Replicate_Ignore_Server_Ids | Content from running the IGNOER_SERVER_IDS option in the CHANGE MASTER TO statement for the slave server |
Master_Server_Id | Master server's server_id value |
Master_UUID | Master server's server_uuid value |
Master_Info_File | Location of the master.info file |
SQL_Delay | Displays by how many seconds the slave server delays the replication with the master server |
SQL_Remaining_Delay | Displays the remaining time when the Slave_SQL_Running_State is Waiting until MASTER_DELAY seconds after master executed event (otherwise, NULL ) |
Slave_SQL_Running_State | Operation status of the SQL thread |
Master_Retry_Count | Number of reconnection attempts when the slave server's connection to the master server is lost |
Master_Bind | Binding network interface for the slave server |
Last_IO_Error_Timestamp | Time of occurrence for the most recent I/O error (YYMMDD HH:MM:SS) |
Last_SQL_Error_Timestamp | Time of occurrence for the most recent SQL error (YYMMDD HH:MM:SS) |
Retrieved_Gtid_Set | GTID group that corresponds to all transactions that the slave server received (left empty when the GTID is not used) |
Executed_Gtid_Set | GTID group created on the binary log (left empty when the GTID is not used) |
Auto_Position | Auto_Position When using, 1, and when not using, 0 |
Troubleshoot replication errors
If the Executed_Gtid_Set
values of the master server and slave server are not identical, then one of the values for Slave_SQL_Running
and Slave_IO_Running
is displayed as No
when the connection status is checked with the show slave status \G;
command. In this case, the following errors may be displayed.
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'e0b74649-41d7-11ec-afb6-f220af895dd6:2' at master log mysql-bin.000001, end_log_pos 751. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Last_IO_Error: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period.
The following describes how to troubleshoot replication errors by editing the Executed_Gtid_Set
value.
To prevent data mismatch when resetting the GTID_PURGED
value, refer to the official documents of MySQL and Percona.
- Run each of the following commands to check if the
Executed_Gtid_Set
values of the master server and slave server are identical.- Master Server:
show global variables like 'gtid_executed';
- Slave Server:
show slave status \G;
- Master Server:
- Run the following command to reset the
GTID_PURGED
value.① Reset GTID_PURGED mysql> stop slave; mysql> reset master; mysql> set global GTID_PURGED="master Executed_Gtid_Set value"; mysql> start slave; ② Reset root account mysql> flush privileges; mysql> create user root@localhost identified by 'password'; mysql> grant all privileges on *.* to root@localhost with grant option; mysql> flush privileges;