RENAME DATABASE name in MySQL

1. “RENAME” command

Syntax：

RENAME DATABASE db_name TO new_db_name

There is the “rename database” command for the early MySQL version, but it was removed after version 5.1.23. It was because the possibility of data lost when using it.

p.s. There shows an error when using "“rename database”:

mysql> rename database shop to mini_shop;  
ERROR 1064 (42000): You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version 
for the right syntax to use 
near 'database shop to mini_shop' at line 1

2. Create a new database, rename all tables of old database to new one. Then drop the old database.

CREATE DATABASE new_db_name;  
RENAME TABLE db_name.table1 TO new_db_name.table1,  
db_name.table2 TO new_db_name.table2;  
DROP DATABASE db_name;

Using Docker to Install MySQL server

1. Go to docker official site, download docker file and install in the local machine.

2. Start docker, type in the following command to ensure docker is running.

   docker images

3. Download MySQL image form docker hub with following commands.

   docker pull mysql/mysql-server

   docker pull mysql/mysql-server:5.7 (version 5.7)

4. Start MySQL server in the container, forward the port out for host connection.

   docker run --name mysqld -e MYSQL_ROOT_PASSWORD=1234 -d -p 3306:3306 mysql/mysql-server

   or

   docker run --name mysql57 -e MYSQL_ROOT_PASSWORD=1234 -d -p 3306:3306 mysql/mysql-server:5.7

   -e: Set environment variables

5. Host connects to MySQL server in docker.

   docker exec -it mysqld bash

   bash> mysql -u root -p (ask for password)

   Using sequel pro , a free GUI to manipulate MySQL server. MySQL Workbench is another good choice!

6. Stop the container.

   docker stop mysqld

7. Start the container.

   docker start mysqld

8. List port mappings or a specific mapping for the container

   docker port mysqld

9. Remove container.

   docker rm mysqld

10. Local host client can not connect to the MySQL server since ‘root’ is limited to localhost connection (within the container).

mysql> select host, user from mysql.user;  
+-----------+---------------+  
| host      |      user     |  
+-----------+---------------+  
| localhost | healthchecker |  
| localhost | mysql.session |  
| localhost | mysql.sys     |  
| localhost | root          |  
+-----------+---------------+  
4 rows in set (0.00 sec)

Change root’s access right for all machines.

mysql> update mysql.user set host='%' where user='root';

mysql> select host, user from mysql.user;  
+-----------+---------------+  
|   host    |     user      |  
+-----------+---------------+  
|    %      |     root      |  
| localhost | healthchecker |  
| localhost | mysql.session |  
| localhost | mysql.sys     |  
+-----------+---------------+  
4 rows in set (0.00 sec)

Restart docker afterward.

docker restart mysql57