HomeWiki

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| nextcloud          |
| performance_schema |
| sitedatabase       |
+--------------------+

MariaDB [(none)]> use sitedatabase;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [sitedatabase]> 

Show all available databases in the current MySQL database server

SHOW DATABASES;

Create a database with a specified name if it does not exist in the database server

CREATE DATABASE [IF NOT EXISTS] database_name;

Use a database or change the current database to another database that you are working with:

USE database_name;

Drop a database with a specified name permanently. All physical files associated with the database will be deleted.

DROP DATABASE [IF EXISTS] database_name;

Show all tables in a current database.

SHOW TABLES;

Create a new table

CREATE TABLE [IF NOT EXISTS] table_name( column_list );

Add a new column into a table:

ALTER TABLE table 
ADD [COLUMN] column_name;

Drop a column from a table:

ALTER TABLE table_name
DROP [COLUMN] column_name;

Add index with a specific name to a table on a column:

ALTER TABLE table 
ADD INDEX [name](column, ...);

Add primary key into a table:

ALTER TABLE table_name 
ADD PRIMARY KEY (column_name,...);

Remove the primary key of a table:

ALTER TABLE table_name
DROP PRIMARY KEY;

Drop a table:

DROP TABLE [IF EXISTS] table_name;

Show the columns of a table:

DESCRIBE table_name;

Show the information of a column in a table:

DESCRIBE table_name column_name;

MariaDB [(none)]> select host, user, password from mysql.user;
+-----------+----------+-------------------------------------------+
| host      | user     | password                                  |
+-----------+----------+-------------------------------------------+
| localhost | root     | *AF926838A9A0501BA7017D135C123C007497F85B |
| localhost | www-data | *AF926838A9A0501BA7017D135C123C007497F85B |
| localhost | nc-user  | *AF926838A9A0501BA7017D135C123C007497F85B |
+-----------+----------+-------------------------------------------+

MariaDB [(none)]> SELECT User, Db, Host from mysql.db;
+----------+--------------+-----------+
| User     | Db           | Host      |
+----------+--------------+-----------+
| nc-user  | nextcloud    | localhost |
| www-data | sitedatabase | localhost |
+----------+--------------+-----------+

CREATE USER 'www-data'@'localhost' IDENTIFIED BY 'your_password';
CREATE USER 'nc-user'@'%' IDENTIFIED BY 'your_password';

Revoke the granted permissions before issuing:

DROP USER 'www-data'@'localhost';
DROP USER 'nc-user'@'%';

MariaDB [(none)]> show grants for 'root'@'localhost';
+------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                      |
+------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                  |
+------------------------------------------------------------------------------------------------+

MariaDB [(none)]> show grants for 'www-data'@'localhost';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for www-data@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'www-data'@'localhost' IDENTIFIED BY PASSWORD '*AF926838A9A0501BA7017D135C123C007497F85B' |
| GRANT ALL PRIVILEGES ON `sitedatabase`.* TO 'www-data'@'localhost'                                              |
+-----------------------------------------------------------------------------------------------------------------+

MariaDB [(none)]> show grants for 'nc-user'@'localhost';
+----------------------------------------------------------------------------------------------------------------+
| Grants for nc-user@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'nc-user'@'localhost' IDENTIFIED BY PASSWORD '*AF926838A9A0501BA7017D135C123C007497F85B' |
| GRANT ALL PRIVILEGES ON `nextcloud`.* TO 'nc-user'@'localhost'                                                 |
+----------------------------------------------------------------------------------------------------------------+

GRANT ALL PRIVILEGES ON `sitedatabase`.* TO 'www-data'@'localhost'
GRANT ALL PRIVILEGES ON `nextcloud`.* TO 'nc-user'@'localhost'

Dump the databases on the original server. Better extract them one-by-one so we don't migrate anything unnecessary. To dump as single databases, use the following commands:

  # mysqldump -u root -p --all-databases > all_databases.sql          <--- [All Databases]
  # mysqldump -u [user] -p --opt [database name] > database_name.sql  <--- [Single Database]
  
  # mysqldump -u root -p --opt sitedatabase > sitedatabase_dump.sql
  # mysqldump -u root -p --opt nextcloud > nextcloud_dump.sql

Once the dump is completed, you are ready to transfer the databases. But databases need to be created first on target server.

  # mysql -u root -p 
  CREATE DATABASE sitedatabase;
  CREATE DATABASE nextcloud;
  quit;

Retore the data to the newly creates databases:

  # mysql -u [user] -p --all-databases < all_databases.sql           <--- [All Databases]
  # mysql -u [user] -p newdatabase < DUMP-SITE-DATABASE.sql          <--- [Single Database]
  
  # mysql -u root -p sitedatabase < sitedatabase_dump.sql
  # mysql -u root -p nextcloud < nextcloud_dump.sql