Ubuntu-fr

Zozo123

Migration mysql 5.5 vers 5.6 suite à la mise à jour 15.04

Bonjour,

Je viens d'effectuer la montée de version de ma Kubuntu en 15.04. J’héberge dessus un site avec une base de données mysql.
Suite à la montée de version de Mysql 5.5 vers 5.6, la base de données n'est plus accessible.

Voici le message d'erreur renvoyé lors de la connexion

$ mysql -u root -p
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Les recherches sur différents forums ne m'ont pas permis de résoudre le problème.
Les fichiers de la version 5.5 ne semblent pas avoir été migrés (pas d'évolution sur la date des fichiers). Ils ne sont pas situés dans le répertoire par défaut mais dans le répertoire suivant : /home/serveur/bdd/mysql/

A noter que le répertoire /var/run/mysqld/  n'existait pas, je l'ai créé et attribué les droits pour "mysql".

Pour info ci-joint le contenu de mon fichier my.cnf que je n'avais modifié que pour la variable "datadir"

# This file has been automatically moved from your previous
# /etc/mysql/my.cnf, with just this comment added at the top, to maintain MySQL
# operation using your previously customised configuration.

# To switch to the new packaging configuration for automated management of
# /etc/mysql/my.cnf across multiple variants:
#
# 1. Move your customisations from this file to /etc/mysql/conf.d/ and
#    to /etc/mysql/<variant>.conf.d/ as appropriate.
# 2. Run "update-alternatives --remove my.cnf /etc/mysql/my.cnf.migrated"
# 3. Remove the file /etc/mysql/my.cnf.migrated

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
#
# * Basic Settings
#
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /home/serveur/bdd/mysql 
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address		= 127.0.0.1
#
# * Fine Tuning
#
key_buffer		= 16M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit	= 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# Here you can see queries with especially long duration
#log_slow_queries	= /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id		= 1
#log_bin			= /var/log/mysql/mysql-bin.log
expire_logs_days	= 10
max_binlog_size         = 100M
#binlog_do_db		= include_database_name
#binlog_ignore_db	= include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet	= 16M

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition

[isamchk]
key_buffer		= 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

Seriez-vous me guider dans la résolution de ce problème ?
Je vous en remercie par avance

EDIT :
J'ai un peu progressé. En vérifiant les paquets installés je me suis aperçu qu'il manquait mysql-server-5.6 (seul mysql-server-core-5.6 était installé).
Je l'ai donc installé, j'ai défini le mot de passe root de mysql mais ensuite le processus c'est bloqué.
Ci joint le détail de l'installation, je ne sais interpréter les messages d'erreur relatif à "InnoDB".
Les droits sur le répertoire /home/serveur/bdd/mysql/ sont attribués à l'utilisateur et au groupe "mysql"

Installation de la nouvelle version du fichier de configuration /etc/init.d/mysql ...
Installation de la nouvelle version du fichier de configuration /etc/init/mysql.conf ...
Installation de la nouvelle version du fichier de configuration /etc/logrotate.d/mysql-server ...
Installation de la nouvelle version du fichier de configuration /etc/mysql/debian-start ...
2015-04-24 21:23:20 0 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
2015-04-24 21:23:20 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-04-24 21:23:20 0 [Note] /usr/sbin/mysqld (mysqld 5.6.24-0ubuntu2) starting as process 10895 ...
2015-04-24 21:23:20 10895 [Warning] Can't create test file /home/serveur/bdd/mysql/ZKUB.lower-test
2015-04-24 21:23:20 10895 [Warning] Can't create test file /home/serveur/bdd/mysql/ZKUB.lower-test
2015-04-24 21:23:20 10895 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
2015-04-24 21:23:20 10895 [Note] Plugin 'FEDERATED' is disabled.
2015-04-24 21:23:20 10895 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-04-24 21:23:20 10895 [Note] InnoDB: The InnoDB memory heap is disabled
2015-04-24 21:23:20 10895 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-04-24 21:23:20 10895 [Note] InnoDB: Memory barrier is not used
2015-04-24 21:23:20 10895 [Note] InnoDB: Compressed tables use zlib 1.2.8
2015-04-24 21:23:20 10895 [Note] InnoDB: Using Linux native AIO
2015-04-24 21:23:20 10895 [Note] InnoDB: Using CPU crc32 instructions
2015-04-24 21:23:20 10895 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-04-24 21:23:20 10895 [Note] InnoDB: Completed initialization of buffer pool
2015-04-24 21:23:20 10895 [ERROR] InnoDB: ./ibdata1 can't be opened in read-write mode
2015-04-24 21:23:20 10895 [ERROR] InnoDB: The system tablespace must be writable!
2015-04-24 21:23:20 10895 [ERROR] Plugin 'InnoDB' init function returned error.
2015-04-24 21:23:20 10895 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2015-04-24 21:23:20 10895 [ERROR] Unknown/unsupported storage engine: InnoDB
2015-04-24 21:23:20 10895 [ERROR] Aborting

2015-04-24 21:23:20 10895 [Note] Binlog end
2015-04-24 21:23:20 10895 [Note] /usr/sbin/mysqld: Shutdown complete

Dernière modification par Zozo123 (Le 24/04/2015, à 21:37)

Zozo123

Re : Migration mysql 5.5 vers 5.6 suite à la mise à jour 15.04

Je me répond car j'ai trouvé la solution.
Le problème était double.

• Le chemin de la variable "datadir" vers les fichiers mysql doit être défini dans le fichier /etc/mysql/my.cnf mais aussi dans /etc/mysql/mysql.conf.d/mysqld.cnf

• Après migration ce chemin ne doit pas pointer vers les anciens fichiers de la version 5.5
  J'ai donc créé un autre répertoire (vide) et demandé la création des fichiers par défaut de la 5.6 avec la commande

  sudo mysql_install_db

  après avoir défini correctement la variable "datadir" dans les 2 fichiers

Ensuite j'ai réinstallé puis configuré de nouveau le paquet mysql-server-5.6 pour définir le mot de passe root de mysql.
Le service mysql est rétabli, il ne reste plus qu'à recharger mes données de ma sauvegarde 5.5 dans les nouveaux fichiers de la 5.6.