mercredi 7 mars 2012

MySQL 5.5 upgrade on Debian Squeeze (2/2)

In the precedent post we have seen how to install and run MySQL 5.5 on a Debian server that was already running MySQL 5.1.
In this post we will go farther and see how we make an init script to start/stop MySQL
Configuration files and scripts are available for download on our git repository at bitbucket
‣ Copy and update Debian configuration files:
Copy both debian.cnf and debian-start from your old /etc/mysql folder or download them from our repository.
/etc# cp mysql/debian.cnf mysql-5.5/
/etc# cp mysql/debian-start mysql-5.5/
Fix paths in debian-start file:
sed -i "s/\/etc\/mysql/\/etc\/mysql-5.5/" /etc/mysql-5.5/debian-start
sed -i "s/\/etc\/init.d\/mysql/\/etc\/init.d\/mysql-5.5/" /etc/mysql-5.5/debian-start
sed -i "s/\/usr\/bin/\/usr\/local\/appservers\/mysql-5.5\/bin/g" /etc/mysql-5.5/debian-start
Fix path into the debian.cnf file.
sed -i "s/\/var\/run\/mysqld\/mysqld.sock/\/var\/run\/mysqld-5.5\/mysqld-5.5.sock/g" /etc/mysql-5.5/debian.cnf
sed -i "s/\/usr$/\/usr\/local\/appservers\/mysql-5.5/g" /etc/mysql-5.5/debian.cnf
‣ make symlink for binary ( to make 'kill' command working )
ln -s /usr/local/appservers/mysql-5.5/bin/mysqld /usr/sbin/mysqld-5.5
‣ Copy your /etc/init.d/mysql to /etc/init.d/mysql-5.5 and fix the init script
Update paths accordingly or use the bash script (update-mysql-init.d) containing sed commands which is available for download in the repository also.
‣ Add the debian-sys-maint user
Please, use the create_debian-sys-maint_user.sql script available on the repository to create debian-sys-maint user.
Make sure to provide the script with the debian-sys-maint password as argument. The password can be found in /etc/mysql-5.5/debian.cnf


Now we can start|stop mysqld 5.5 with the following command:
#start the server
/etc/init.d/mysql-5.5 start

#stop the server
/etc/init.d/mysql-5.5 stop
‣ Make sure we can connect to your server
/usr/local/appservers/mysql-5.5/bin/mysql -S/var/run/mysqld-5.5/mysqld-5.5.sock -uroot

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)
‣ Configure MySQL 5.5 to start when system boots
Debian uses a Sys-V like init system for executing commands when the system runlevel changes - for example at bootup and shutdown time.
# update-rc.d mysql-5.5 defaults
check that the command was successful:
/etc$ find rc* -regex .*mysql.*

rc0.d/K01mysql-5.5
rc0.d/K02mysql

rc1.d/K01mysql-5.5  
rc1.d/K02mysql

rc2.d/S20mysql-5.5
rc2.d/S20mysql

rc3.d/S20mysql-5.5
rc3.d/S20mysql

rc4.d/S20mysql-5.5
rc4.d/S20mysql

rc5.d/S20mysql-5.5
rc5.d/S20mysql

rc6.d/K01mysql-5.5
rc6.d/K02mysql
Now, launch the server with /etc/init.d/mysql-5.5 start then reboot the server to make sure that MySQL 5.5 will start too.
If the server didn't start at boot time, you should start looking in /var/log/syslog and in log file /usr/local/appservers/mysql-5.5/data/host_name.err
‣ Create an alias to connect to MySQL 5.5
Added this alias to your bash.bashrc file (make it system wide)
alias mysql-5.5='/usr/local/appservers/mysql-5.5/bin/mysql -S /var/run/mysqld-5.5/mysqld-5.5.sock '
You can now connect to your server by opening a new terminal and issuing:
mysql-5.5 -uroot -p
‣ Change root password
Before we continue, let's change the root password. More information here
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('newpwd');
Try to connect to make sure that password was changed. You'll have to use the -p parameter.
/usr/local/appservers/mysql-5.5/bin/mysql \
-S/var/run/mysqld-5.5/mysqld-5.5.sock \
-uroot -p
If you are using mysql-query-browser, you must go to advanced setting of the "create connection" window, and specify this socket /var/run/mysqld-5.5/mysqld-5.5.sock
‣ Drop test database
mysql> Drop database test;
‣ Delete anonymous account
DROP USER ''@'localhost';
DROP USER ''@'host_name';

select host, user, password from user;
‣ Finally, import you data to the new server
To keep this post simple, we will see how to import one database only

First dump the database from the old MySQL 5.1 server (to dump all databases replace the --databases flag by --all-databases):
mysqldump -u root -p --databases my_db > /home/backups/my_db_dump.sql
Then, import it into the new MySQL 5.5 server:
mysql -uroot -p -S /var/run/mysqld-5.5/mysqld-5.5.sock < /home/backups/my_db_dump.sql
In my case, I had to create a user to be associated with this db:

CREATE USER 'reda'@'localhost' IDENTIFIED BY 'my_pass';
GRANT ALL ON my_db.* TO 'reda'@'localhost';

CREATE USER 'reda'@'127.0.0.1' IDENTIFIED BY 'my_pass';
GRANT ALL ON my_db.* TO 'reda'@'127.0.0.1';

‣ Conclusion
During these two posts we have seen how to install MySQL 5.5 on a Debian Squeeze server that was already running a MySQL 5.1 server.

We did an init.d script to start and stop MySQL as any other service. We was able to make it start at boot time and stop at system shutdown.

If you need to move from utf8 to utf8mb4, you may find the next post useful.

Configuration files and scripts are available for download on our git repository at bitbucket


‣Resources:
http://www.dotdeb.org/2012/02/09/mysql-5-5-20/
http://www.ovaistariq.net/490/a-step-by-step-guide-to-upgrading-to-mysql-5-5/
http://www.ducea.com/2009/01/19/running-multiple-instances-of-mysql-on-the-same-machine/
http://www.ducea.com/2009/01/19/running-multiple-instances-of-mysql-on-the-same-machine/#comment-193000

Aucun commentaire:

Enregistrer un commentaire