Basic MySQL administration, and useful commands.
Locating the directory with the database files
root@lthlibpub:/etc/mysql# mysqladmin -u root -p variables | grep datadir
Enter password:
| datadir | /var/lib/mysql/
root@lthlibpub:/etc/mysql# ll /var/lib/mysql
total 185M
-rw-r----- 1 mysql mysql 56 Mar 3 2019 auto.cnf
drwxr-x--- 2 mysql mysql 4.0K Apr 3 2019 bolt
-rw------- 1 mysql mysql 1.7K Nov 25 2019 ca-key.pem
(...)
drwxr-x--- 2 mysql mysql 4.0K Dec 31 10:42 lsd
drwxr-x--- 2 mysql mysql 4.0K Feb 25 18:17 mysql
Listing the databases
root@lthlibpub:/var/lib/mysql# mysql -u root -p
(...)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bolt |
| lsd |
| mysql |
| performance_schema |
| phpmyadmin |
| rrds |
| sys |
+--------------------+
8 rows in set (0.00 sec)
mysql>
Showing grants for a user
mysql> show grants for 'mysqlbkp'@'10.127.4.56';
+-----------------------------------------------------------------------+
| Grants for mysqlbkp@10.127.4.56 |
+-----------------------------------------------------------------------+
| GRANT SELECT, PROCESS, LOCK TABLES ON *.* TO `mysqlbkp`@`10.127.4.56` |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
To remove grants
mysql> revoke all on *.* from `mysqlbkp`@`10.127.4.56`;
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for 'mysqlbkp'@'10.127.4.56';
+------------------------------------------------+
| Grants for mysqlbkp@10.127.4.56 |
+------------------------------------------------+
| GRANT USAGE ON *.* TO `mysqlbkp`@`10.127.4.56` |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql>
Giving grants to a user to access the database
mysql> grant alter,create,delete,drop,index,insert,select,update,trigger,alter routine,\
create routine, execute, create temporary tables on lsd.* to 'lsd'@'%';
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql> grant alter,create,delete,drop,index,insert,select,update,trigger,alter routine,\
create routine, execute, create temporary tables on lsd.* to 'lsd'@'localhost';
Query OK, 0 rows affected, 1 warning (0.07 sec)
mysql>
To remove a user
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| lsd | % |
(...)
mysql>
mysql> drop user 'lsd'@'%';
Query OK, 0 rows affected (0.09 sec)
mysql>
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| root | % |
| mysql.infoschema | localhost |
(...)
Creating a user with local and remote access
mysql>
mysql> create user 'lsd'@'localhost' identified with mysql_native_password by 'my_password';
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> create user 'lsd'@'%' identified with mysql_native_password by 'my_password';
Query OK, 0 rows affected (0.07 sec)
mysql>
List the users on the MySQL server
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| lsd | % |
| root | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)
mysql>
Doing a dump of the database
root@lthlibpub:~# mysqldump -u root -p lsd > lsd.sql
Enter password:
root@lthlibpub:~#
root@lthlibpub:~# ls -lhtr
total 381M
(...)
-rw-r----- 1 root root 352M May 23 15:21 lsd.sql
root@lthlibpub:~#
Create the user
mysql> CREATE USER 'mysqlbkp'@'10.10.10.10' IDENTIFIED BY '<some password>';
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
Grant permissions to the backup user
mysql> GRANT SELECT ON *.* TO 'mysqlbkp'@'<mysql client>';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT PROCESS, SELECT, LOCK TABLES ON *.* TO 'mysqlbkp'@'<mysql client>';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> grant show view on *.* to `mysqlbkp`@`10.127.4.59`;
Query OK, 0 rows affected (0.01 sec)
mysql>
The following permissions are important for remote backup
mysql> grant event on *.* to `mysqlbkp`@`10.127.4.59`;
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for 'mysqlbkp'@'10.127.4.59';
+-----------------------------------------------------------------------------------------+
| Grants for mysqlbkp@10.127.4.59 |
+-----------------------------------------------------------------------------------------+
| GRANT SELECT, PROCESS, LOCK TABLES, SHOW VIEW, EVENT ON *.* TO `mysqlbkp`@`10.127.4.59` |
+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
Test the dump of a database
a-garcm0b@lthlibtest:~$ mysqldump --column-statistics=0 -h <mysql server> -u mysqlbkp -p repositoryMiscellaneous > repositoryMiscellaneous.sql
a-garcm0b@lthlibtest:~$
If the connection fails,
a-garcm0b@lthlibtest:~$ mysqldump --column-statistics=0 -h 10.127.4.55 -u mysqlbkp -p repositoryMiscellaneous > repositoryMiscellaneous.sql
Enter password:
mysqldump: Got error: 2003: Can't connect to MySQL server on '10.127.4.55:3306' (111) when trying to connect
a-garcm0b@lthlibtest:~$
check if bind-address is binded to loopback IP only. If yes, edit the configuration file mysqld.cnf, and restart MySQL
a-garcm0b@lthlibprod:/etc/mysql/mysql.conf.d$ sudo vim mysqld.cnf
(...)
#bind-address = 127.0.0.1
#mysqlx-bind-address = 127.0.0.1
(...)
a-garcm0b@lthlibprod:/etc/mysql/mysql.conf.d$ sudo systemctl restart mysql
Importing data from a MySQL dump
a-garcm0b@lthlibprod2:~/prod_sqldump$ sudo mysql -u root ioi < ioi.sql
Remove a database with command drop
mysql> drop database doiMinter;
Query OK, 0 rows affected (0.01 sec)
Show the size of the databases
mysql> SELECT
-> table_schema 'Database Name',
-> SUM(data_length + index_length) 'Size in Bytes',
-> ROUND(SUM(data_length + index_length) / (1024 * 1024), 2) 'Size in MiB'
-> FROM information_schema.tables
-> GROUP BY table_schema;
+-----------------------------------+---------------+-------------+
| Database Name | Size in Bytes | Size in MiB |
+-----------------------------------+---------------+-------------+
| information_schema | 163840 | 0.16 |
| ioi | 4489445376 | 4281.47 |
| irts | 25800097792 | 24604.89 |
| lsd | 180224 | 0.17 |
| mysql | 2727388 | 2.60 |
| pcounter | 267747328 | 255.34 |
| performance_schema | 0 | 0.00 |
| repositoryAuthorityControl | 57802768 | 55.13 |
| repositoryExternalMetadataSources | 56705024 | 54.08 |
| repositoryMiscellaneous | 2014264 | 1.92 |
| repositoryTemp | 22867728 | 21.81 |
| sys | 16384 | 0.02 |
+-----------------------------------+---------------+-------------+
14 rows in set (0.18 sec)
mysql>
Running and connecting to a simple MySQL container
Create the container
mgarcia@wsl2:~$ docker run -d \
> --name learn_mysql \
> -v v_learn_mysql:/var/lib/mysql \
> -e MYSQL_ROOT_PASSWORD=secret \
> -e MYSQL_DATABASE=learn \
> mysql
Check if the container is running
mgarcia@wsl2:~$ docker container ls
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
ea779bef45d0 mysql "docker-entrypoint.s…" 44 seconds ago Up 42 seconds 3306/tcp, 33060/tcp learn_mysql
mgarcia@wsl2:~$
Connect to the container
mgarcia@wsl2:~$ docker exec -it learn_mysql mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.27 MySQL Community Server - GPL
(...)
mysql>
Create the container
PS C:\Users\garcm0b\Work\lsdd\sql_dump> docker run -d --name lsdd_db -v sql_dump:/docker-entrypoint-initdb.d -e MYSQL_ROOT_PASSWORD=my-secret-pw -e MYSQL_DATABASE=lsd mysql --default-authentication-plugin=mysql_native_password
1a7233e94df6c24d016ed6d77c304a82e5a187a976945b4ace550701397fbd62
PS C:\Users\garcm0b\Work\lsdd\sql_dump>
Double check the container is running
PS C:\Users\garcm0b\Work\lsdd\sql_dump> docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
1a7233e94df6 mysql "docker-entrypoint.s…" 5 seconds ago Up 5 seconds 3306/tcp, 33060/tcp lsdd_db
PS C:\Users\garcm0b\Work\lsdd\sql_dump>
Connect to the container
PS C:\Users\garcm0b\Work\lsdd\sql_dump> docker exec -it lsdd_db mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8...
(...)
Import the SQL dump
PS C:\Users\garcm0b\Work\lsdd\sql_dump> $Env:MYSQL_ROOT_PASSWORD='my-secret-pw'
PS C:\Users\garcm0b\Work\lsdd\sql_dump> get-content lsd.sql | docker exec -i lsdd_db sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD" lsd '
mysql: [Warning] Using a password on the command line interface can be insecure.
PS C:\Users\garcm0b\Work\lsdd\sql_dump>
Note
In Powershell the input redirection doesn't work, so we have to use theget-contentcommandlet to cat the content of the file.