MYSQL
Le
Zuthos

Bonjour,
J'ai un gros soucis avec MYSQL.
Impossible de me connecter à cette dernière.
Tous les services utilisant cette derniére ne fonctionne plus. ;-(
Tous les utilisateurs, y compris administrateur sont éjectés.
D'ou mes questions:
Puis-je récupérer et sauvegarder les tables de ma base de donnÃ=
©es?
Puis-je remetre ma base en ordre de marche
--
J'ai un gros soucis avec MYSQL.
Impossible de me connecter à cette dernière.
Tous les services utilisant cette derniére ne fonctionne plus. ;-(
Tous les utilisateurs, y compris administrateur sont éjectés.
D'ou mes questions:
Puis-je récupérer et sauvegarder les tables de ma base de donnÃ=
©es?
Puis-je remetre ma base en ordre de marche
--
Voici le message losque j'essaye de me connectyer a roundcube:
DATABASE ERROR: CONNECTION FAILED!
Unable to connect to the database!
Please contact your server-administrator.
Avec phpmyadmin:
mysqli_real_connect(): (HY000/1698): Access denied for user
'zuthos'@'localhost'
#1698 - Access denied for user 'zuthos'@'localhost'
Je ne sais pas ou regarder ailleur
le fichier de log:
2019-03-01 14:16:41 140382442050688 [Note] InnoDB: innodb_empty_free_list_algorithm has been changed to legacy because of small buffer pool size. In order to use backoff, increase buffer pool at least up to 20MB.
2019-03-01 14:16:42 140382442050688 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2019-03-01 14:16:42 140382442050688 [Note] InnoDB: The InnoDB memory heap is disabled
2019-03-01 14:16:42 140382442050688 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-03-01 14:16:42 140382442050688 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-03-01 14:16:42 140382442050688 [Note] InnoDB: Compressed tables use zlib 1.2.8
2019-03-01 14:16:42 140382442050688 [Note] InnoDB: Using Linux native AIO
2019-03-01 14:16:42 140382442050688 [Note] InnoDB: Using SSE crc32 instructions
2019-03-01 14:16:42 140382442050688 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2019-03-01 14:16:42 140382442050688 [Note] InnoDB: Completed initialization of buffer pool
2019-03-01 14:16:42 140382442050688 [Note] InnoDB: Highest supported file format is Barracuda.
2019-03-01 14:16:42 140382442050688 [Note] InnoDB: The log sequence number 1616737 in ibdata file do not match the log sequence number 1616777 in the ib_logfiles!
2019-03-01 14:16:42 140382442050688 [Note] InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
2019-03-01 14:16:43 140382442050688 [Note] InnoDB: 128 rollback segment(s) are active.
2019-03-01 14:16:43 140382442050688 [Note] InnoDB: Waiting for purge to start
2019-03-01 14:16:43 140382442050688 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.41-84.1 started; log sequence number 1616777
2019-03-01 14:16:43 140382442050688 [Note] Plugin 'FEEDBACK' is disabled.
2019-03-01 14:16:43 140381807179520 [Note] InnoDB: Dumping buffer pool(s) not yet started
2019-03-01 14:16:43 140382442050688 [Note] Recovering after a crash using tc.log
2019-03-01 14:16:43 140382442050688 [Note] Starting crash recovery...
2019-03-01 14:16:43 140382442050688 [Note] Crash recovery finished.
2019-03-01 14:16:43 140382442050688 [Note] Server socket created on IP: '127.0.0.1'.
2019-03-01 14:16:44 140382442050688 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.1.37-MariaDB-0+deb9u1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Debian 9.6
2019-03-01 14:32:26 140382441264896 [Note] /usr/sbin/mysqld: Normal shutdown
2019-03-01 14:32:26 140382441264896 [Note] Event Scheduler: Purging the queue. 0 events
2019-03-01 14:32:26 140381874267904 [Note] InnoDB: FTS optimize thread exiting.
2019-03-01 14:32:26 140382441264896 [Note] InnoDB: Starting shutdown...
2019-03-01 14:32:27 140382441264896 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool
2019-03-01 14:32:28 140382441264896 [Note] InnoDB: Shutdown completed; log sequence number 1616787
2019-03-01 14:32:28 140382441264896 [Note] /usr/sbin/mysqld: Shutdown complete
2019-03-01 14:32:29 140231966717056 [Note] InnoDB: innodb_empty_free_list_algorithm has been changed to legacy because of small buffer pool size. In order to use backoff, increase buffer pool at least up to 20MB.
2019-03-01 14:32:29 140231966717056 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2019-03-01 14:32:29 140231966717056 [Note] InnoDB: The InnoDB memory heap is disabled
2019-03-01 14:32:29 140231966717056 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-03-01 14:32:29 140231966717056 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-03-01 14:32:29 140231966717056 [Note] InnoDB: Compressed tables use zlib 1.2.8
2019-03-01 14:32:29 140231966717056 [Note] InnoDB: Using Linux native AIO
2019-03-01 14:32:29 140231966717056 [Note] InnoDB: Using SSE crc32 instructions
2019-03-01 14:32:29 140231966717056 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2019-03-01 14:32:29 140231966717056 [Note] InnoDB: Completed initialization of buffer pool
2019-03-01 14:32:29 140231966717056 [Note] InnoDB: Highest supported file format is Barracuda.
2019-03-01 14:32:29 140231966717056 [Note] InnoDB: 128 rollback segment(s) are active.
2019-03-01 14:32:29 140231966717056 [Note] InnoDB: Waiting for purge to start
2019-03-01 14:32:29 140231966717056 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.41-84.1 started; log sequence number 1616787
2019-03-01 14:32:29 140231966717056 [Note] Plugin 'FEEDBACK' is disabled.
2019-03-01 14:32:29 140231349106432 [Note] InnoDB: Dumping buffer pool(s) not yet started
2019-03-01 14:32:29 140231966717056 [Note] Server socket created on IP: '127.0.0.1'.
2019-03-01 14:32:29 140231966717056 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.1.37-MariaDB-0+deb9u1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Debian 9.6
2019-03-01 14:42:38 140231932110592 [Note] /usr/sbin/mysqld: Normal shutdown
2019-03-01 14:42:38 140231412012800 [Note] InnoDB: FTS optimize thread exiting.
2019-03-01 14:42:38 140231932110592 [Note] InnoDB: Starting shutdown...
2019-03-01 14:42:39 140231932110592 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool
2019-03-01 14:42:41 140231932110592 [Note] InnoDB: Shutdown completed; log sequence number 1616797
2019-03-01 14:42:41 140231932110592 [Note] /usr/sbin/mysqld: Shutdown complete
2019-03-01 14:42:42 139992997708928 [Note] InnoDB: innodb_empty_free_list_algorithm has been changed to legacy because of small buffer pool size. In order to use backoff, increase buffer pool at least up to 20MB.
2019-03-01 14:42:42 139992997708928 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2019-03-01 14:42:42 139992997708928 [Note] InnoDB: The InnoDB memory heap is disabled
2019-03-01 14:42:42 139992997708928 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-03-01 14:42:42 139992997708928 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-03-01 14:42:42 139992997708928 [Note] InnoDB: Compressed tables use zlib 1.2.8
2019-03-01 14:42:42 139992997708928 [Note] InnoDB: Using Linux native AIO
2019-03-01 14:42:42 139992997708928 [Note] InnoDB: Using SSE crc32 instructions
2019-03-01 14:42:42 139992997708928 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2019-03-01 14:42:42 139992997708928 [Note] InnoDB: Completed initialization of buffer pool
2019-03-01 14:42:42 139992997708928 [Note] InnoDB: Highest supported file format is Barracuda.
2019-03-01 14:42:42 139992997708928 [Note] InnoDB: 128 rollback segment(s) are active.
2019-03-01 14:42:42 139992997708928 [Note] InnoDB: Waiting for purge to start
2019-03-01 14:42:42 139992997708928 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.41-84.1 started; log sequence number 1616797
2019-03-01 14:42:42 139992997708928 [Note] Plugin 'FEEDBACK' is disabled.
2019-03-01 14:42:42 139992387024640 [Note] InnoDB: Dumping buffer pool(s) not yet started
2019-03-01 14:42:42 139992997708928 [Note] Server socket created on IP: '127.0.0.1'.
2019-03-01 14:42:42 139992997708928 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.1.37-MariaDB-0+deb9u1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Debian 9.6
Ce qui me fais peur c'est cela:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
J'ai l'impression que toutes mes bases ont disparu.
Bonjour,
Et merci. C'est un énorme soulagement pour moi.
J'ai effectivement retrouvé mes bases. Elles sont dans le repertoire /var/lib/mysql-5.7 au lieu de /var/lib/mysql
J'ai essayé d'intervertir les deux repertoires afin de tout remettre
dans l'ordre. Malheureusement, cela ne c'est pas bien passé:
# service mysql restart
Job for mariadb.service failed because the control process exited with
error code.
See "systemctl status mariadb.service" and "journalctl -xe" for details.
Je ne sais pas trop quoi faire.
Le samedi 02 mars 2019, Zuthos a écrit...
As tu fait le `journalctl -xe` ?
Que donne `rgrep datadir /etc/mysql` (ou `ag datadir /etc/mysql` si tu as le
paquet silversearcher-ag installé) ?
--
jm
voici le résultat de `journalctl -xe` :
-- Subject: L'unité (unit) mariadb.service a commencé à démarrer
-- Defined-By: systemd
-- Support: https://www.debian.org/support
--
-- L'unité (unit) mariadb.service a commencé à démarrer.
mars 02 14:03:55 oddy.palourde.net mysqld[19132]: 2019-03-02 14:03:55
140148007892096 [Note] /usr/sbin/mysqld (mysqld
10.1.37-MariaDB-0+deb9u1) starting as process 19132 ...
mars 02 14:03:55 oddy.palourde.net mysqld[19132]: 2019-03-02 14:03:55
140148007892096 [Warning] Can't create test file
/var/lib/mysql/oddy.lower-test
mars 02 14:03:55 oddy.palourde.net mysqld[19132]: [90B blob data]
mars 02 14:03:55 oddy.palourde.net mysqld[19132]: 2019-03-02 14:03:55
140148007892096 [ERROR] Aborting
mars 02 14:03:55 oddy.palourde.net systemd[1]: mariadb.service: Main
process exited, code=exited, status=1/FAILURE
mars 02 14:03:55 oddy.palourde.net systemd[1]: Failed to start MariaDB
10.1.37 database server.
-- Subject: L'unité (unit) mariadb.service a échoué
-- Defined-By: systemd
-- Support: https://www.debian.org/support
--
-- L'unité (unit) mariadb.service a échoué, avec le résultat failed.
mars 02 14:03:55 oddy.palourde.net systemd[1]: mariadb.service: Unit
entered failed state.
mars 02 14:03:55 oddy.palourde.net systemd[1]: mariadb.service: Failed
with result 'exit-code'.
ultat de
# rgrep datadir /etc/mysql
/etc/mysql/mariadb.conf.d/50-server.cnf:datadir = /var/lib/mysql
/etc/mysql/mysql.conf.d/mysqld.cnf:datadir = /var/lib/mysql
/etc/mysql/mysql.conf.d/mysqld.cnf.dpkg-dist:datadir /var/lib/mysql
Le samedi 02 mars 2019, Zuthos a écrit...
Tu devrais tout remettre comme c'était, puisque ton datadir est sur
/var/lib/mysql, afin de faire démarrer mysqld en premier lieu.
Le warning dans le démarrage de mysql est étrange : regarde les droits sur
le répertoire /var/lib/mysql et sur ce qu'il y a dedans. Ta partition n'est
pas pleine ?
Ensuite, tu pourras voir pour les autres bases. Normalement, les bases sont
dans des répertoires sous /var/lib/mysql (donc la base 'toto' dans
/var/lib/mysql/toto avec les tables en .frm et .ibd).
Essaie de le lancer en ligne de commande :
mysqld_safe --skip-grant-tables 2>&1
Et voir si ça démarre/affiche quelque chose.
--
jm
un df -H m'indique une aprtition utilisé à 89%, reste 2.9G
les droits sont mysq:mysql
# mysqld_safe --skip-grant-tables 2>&1
190302 20:59:07 mysqld_safe Logging to syslog.
190302 20:59:07 mysqld_safe Starting mysqld daemon with databases from
/var/lib/mysql
Le samedi 02 mars 2019, Zuthos a écrit...
D'accord. Mais encore ? Tu peux te connecter dessus ? Car, chez moi, lorsque
je fais la manip je me connecte sans problème à mes bases, dont mysql elle
même.
--
jm
Le samedi 02 mars 2019, Zuthos a écrit...
Tu devrais également vérifier les permissions.
/var/lib/mysql devrait être mysql:mysql en 755
Les fichiers dedans en 660.
Les répertoires de bdd en 700. Les fichiers dans ces répertoires en 660
également.
--
jm
Il ne m'est pas possible de me connecter
# ddmysql -h localhost -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket
'/var/run/mysqld/mysqld.sock' (2 "No such file or directory")