I am trying to backup a small database (15.7 KiB) using the below command line and keep hanging, keep in mind that there is nothing running the background, either no PHP queries:
mysql -u root -pPASSWORD compet > /home/user/www/compet.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
Running the process list:
mysql> show processlist;
+----+-----------------+-----------+--------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+--------+---------+------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 269 | Waiting on empty queue | NULL |
| 8 | root | localhost | compet | Sleep | 235 | | NULL |
| 13 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-----------------+-----------+--------+---------+------+------------------------+------------------+
3 rows in set (0.00 sec)
I restarted MySQL many times, still the same problem.
sys.log:
Dec 3 21:22:39 nsXXXX systemd[1]: Stopping MySQL Community Server...
Dec 3 21:22:43 nsXXXX systemd[1]: mysql.service: Succeeded.
Dec 3 21:22:43 nsXXXX systemd[1]: Stopped MySQL Community Server.
Dec 3 21:22:43 nsXXXX systemd[1]: Starting MySQL Community Server...
Dec 3 21:22:44 nsXXXX systemd[1]: Started MySQL Community Server.
mysql error.log:
2020-12-03T21:22:41.993028Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 8 user: 'root'.
2020-12-03T21:22:41.993158Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 10 user: 'root'.
2020-12-03T21:22:43.252856Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.22-0ubuntu0.20.10.2) (Ubuntu).
2020-12-03T21:22:43.587092Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.22-0ubuntu0.20.10.2) starting as process 18545
2020-12-03T21:22:43.595246Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-12-03T21:22:44.266413Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-12-03T21:22:44.405846Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2020-12-03T21:22:44.626198Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-12-03T21:22:44.626556Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2020-12-03T21:22:44.648645Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.22-0ubuntu0.20.10.2' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu).
It takes a long time without any result
For do database backup use mysqldump not mysql:
mysqldump -u root -pPASSWORD compet > /home/user/www/compet.sql
For restore data from backup use mysql command:
mysql -u root -pPASSWORD compet < /home/user/www/compet.sql