Hot Install/Upgrade Mysql from sources, with minimal downtime #MySQL

La siguiente entrada cubre los pasos a seguir para realizar el Upgrade de Mysql Server 5.1.x a 5.5.21 con un tiempo mínimo de downtime. Es decir, se pretende hacer el upgrade en caliente del motor de base de datos perdiendo a lo sumo algunas pocas conexiones, en un sitio con una carga media-alta.
WorkFlowy: http://goo.gl/W4nGP

• Descargar Mysql mysql-5.5.21.tar.gz
  NOTA: http://goo.gl/EwAK
• tar -xzvf mysql-5.5.21.tar.gz
• cd mysql-5.5.21/
• => For Ubuntu 8.04 Hardy Heron - Upgrade Distribution
  NOTA: para compilar MySQL se requiere CMake =>2.6. Hardy llega hasta la es versión 2.4.7 de CMake.
  • shell> sudo apt-get install update-manager-core
  • shell> vi /etc/update-manager/release-upgrades
    NOTA: Cambiar Prompt=normal
  • shell> sudo do-release-upgrade
    NOTA: Hacer un Snapshot previo a realizar el upgrade.
  • Resar 10 Ave Maria y 2 Padre Nuestros !!!
• apt-get install cmake make gcc libncurses5-dev g++ libaio-dev
• mdkir bld; cd bld
  NOTA: http://goo.gl/5sliN
• mkdir -p /usr/local/mysql-5.5.21
• cmake -DBUILD_CONFIG=mysql_release -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_EMBEDDED_SERVER=1 -DCMAKE_INSTALL_PREFIX="/usr/local/mysql-5.5.21" ../
  NOTA: Si cmake falla, borrar el archivo CMakeCache.txt en el directorio actual. Luego volver a ejecutar cmake con todas las banderas.
• make install
• make test
• make test-force
  NOTA: Paso opcional, recomendable. Tarda 1hr aprox en hacer todos los tests.
• => 2.10.1. Unix Postinstallation Procedures
  • shell> cd /usr/local/mysql-5.5.21
  • shell> /usr/local/mysql-5.5.21# /usr/local/mysql-5.5.21/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql-5.5.21 --datadir=/usr/local/mysql-5.5.21/data/ --no-defaults
    WARNING: The host 'hostname' could not be looked up with resolveip. This probably means that your libc libraries are not 100 % compatible with this binary MySQL version. The MySQL daemon, mysqld, should work normally with the exception that host name resolving will not work. This means that you should use IP addresses instead of hostnames when specifying MySQL privileges ! Installing MySQL system tables... OK Filling help tables... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/local/mysql-5.5.21/bin/mysqladmin -u root password 'new-password' /usr/local/mysql-5.5.21/bin/mysqladmin -u root -h `hostname` password 'new-password' Alternatively you can run: /usr/local/mysql-5.5.21/bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd /usr/local/mysql-5.5.21 ; /usr/local/mysql-5.5.21/bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd /usr/local/mysql-5.5.21/mysql-test ; perl mysql-test-run.pl Please report any problems with the /usr/local/mysql-5.5.21/scripts/mysqlbug script!
  • shell> chown -R root .
  • shell> chown -R mysql data/
  • shell> cd /usr/local/mysql-5.5.21/support-files
  • shell> cp my-small.cnf /usr/local/mysql-5.5.21/my.cnf
  • shell> vi /usr/local/mysql-5.5.21/my.cnf
    CAMBIAR VALORES PARA LOS TAGs [client] y [mysqld]
    • port = 3306 x port = 3307
    • Agregar:
      • user=mysql
      • log-error = /usr/local/mysql-5.5.21/data/error.log
  • shell> /usr/local/mysql-5.5.21/bin/mysqld_safe --defaults-file=/usr/local/mysql-5.5.21/support-files/my.cnf --user=mysql &
    NOTA: Esta es la nueva instancia, la que sera productiva. Levantamos en otro puerto para hacer restore de todas las base de datos que están productivas.
  • shell> /usr/local/mysql-5.5.21/bin/mysqladmin -u root -h hostname password 'new-password' -P 3307
    NOTA: SET root password
  • shell> mysql -u root -p -P 3307 -h hostname
    NOTA: Test conexion con la nueva instancia. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.21 MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
    • mysql> show databases;
    • mysql> show variabales;
    • mysql> create user 'test'@'%';
    • mysql> grant all privileges on test.* to 'test'@'%';
    • mysql> flush privileges;
    • mysql>quit;
  • /usr/local/mysql-5.5.21/bin/mysqladmin version -h `hostname` -P 3307 -u root -p
    Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Server version 5.5.21 Protocol version 10 Connection hostname via TCP/IP TCP port 3307 Uptime: 42 min 34 sec Threads: 1 Questions: 10 Slow queries: 0 Opens: 35 Flush tables: 1 Open tables: 4 Queries per second avg: 0.003
  • /usr/local/mysql-5.5.21/bin/mysql -h `hostname` -P 3307 -p -e "SELECT Host,Db,User FROM db" mysql
    +------+---------+------+ | Host | Db | User | +------+---------+------+ | % | test | | | % | test\_% | | +------+---------+------+
  • /usr/local/mysql-5.5.21/bin/mysqladmin -P 3307 -h `hostname` -u root -p shutdown
    NOTA: shutdown el server.
  • shell> cd /usr/local/mysql-5.5.21/sql-bench
  • shell> perl run-all-tests --user='test' --socket='/tmp/mysql.sock'
• => 2.10.1.2, "Starting and Stopping MySQL Automatically.
  • shell> cp /usr/local/mysql-5.5.21/support-files/mysql.server /etc/init.d/mysql-5.5.21
  • shell> chmod +x /etc/init.d/mysql-5.5.21
  • shell> vi /etc/init.d/mysql-5.5.21
    NOTA: Cambiar
    • Linea 248:
      • extra_args="-e $basedir/my.cnf"
      • extra_args="-c $basedir/my.cnf"
    • Linea 252:
      • extra_args="-e $datadir/my.cnf"
      • extra_args="-c $datadir/my.cnf"
    • Linea 283:
      NOTA: Por errores, cuando no se instala, en el directorio por defecto. Console Output: 120309 20:28:40 mysqld_safe Can't log to error log and syslog at the same time. Remove all --log-error configuration options for --syslog to take effect. error log: http://goo.gl/VfKfa
      • $bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 &
      • $bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args --language=$basedir/share/english/ >/dev/null 2>&1 &
  • shell> ln -s /etc/init.d/mysql-5.5.21 /etc/rc3.d/S99mysql
    NOTA: # Usually this is put in /etc/init.d (at least on machines SYSV R4 based # systems) and linked to /etc/rc3.d/S99mysql and /etc/rc0.d/K01mysql. # When this is done the mysql server will be started when the machine is # started and shut down when the systems goes down.
  • shell> ln -s /etc/init.d/mysql-5.5.21 /etc/rc0.d/K01mysql
• => Backup/Restore MySQL-Productive DBs to New-MySQL
  • Lock Read All Productive DataBases
    NOTA: Abrimos una nueva terminal, ejecutamos lo siguiente y dejamos abierta la terminal.
    • shell> mysql -u root -h `hostname` -P 3306 -p
    • mysql> FLUSH TABLES WITH READ LOCK;
      When locking the tables with FLUSH TABLES WITH READ LOCK; do not quit the mysql shell else you will lose the lock, use another shell to do the db dump instead.
  • Backup all Productives DataBases
    • mysqldump --all-databases -p --port=3306 -h `hostname` --lock-all-tables > BackupProd"`date +%d-%m-%y`".sql
  • Restore all DataBases in New Instance
    NOTA: En otra shell ejecutamos lo siguiente:
    • shell> mysql -u root -P 3307 -h `hostname` -p < BackupProd"`date +%d-%m-%y`".sql
• => Exchange MySQL Instances.
  • Exchange Listeners ports to ProductiveDB & New-Instance
    • shell> vi /etc/my.cnf
      • Change port=3306 x port=3308
    • shell> vi /usr/local/mysql-5.5.21/my.cnf
      • Change port=3307 x port=3306
    • Reload MySQLs Services
      • shell> /etc/init.d/mysql reload && /etc/mysql-5.5.21 reload
  • Apagar Old-Instance
    • shell> netstat -anp | grep ":3308" | grep -v grep | wc -l
      NOTA: Si en este punto, acusa solamente el puerto del servicio en escucha, continuar con el siguientes pasos. Sino esperar que cierren todas las conexiones.
    • /etc/init.d/mysql stop
      NOTA: Paramos la vieja instancia.
    • rm /etc/init.d/mysql
      NOTA: Eliminamos script de inicio de la vieja instancia.
• 2.10.2, "Securing the Initial MySQL Accounts
• 9.6, "MySQL Server Time Zone Support."

La ultima versión, con correcciones y modificaciones en WorkFly;  http://goo.gl/W4nGP