Installing Multiple Versions of MySQL
Posted at Wednesday, February 20, 2008
As with any RDBMS, there are circumstances in which you need to run multiple, differing versions on the same server.
This posts reviews my preferred way of accomplishing this for MySQL on Linux. The server in question already has a previous 4.X.X version of MySQL. Rather than overwrite this, I would like to install 5.1.23, decoupling it from the existing MySQL installation. In order to do this, I will be compiling MySQL from a source distribution specifying a custom TCP port as well as custom file locations, etc. Following this scheme, you could install many different versions of MySQL on a single server.
Here are the steps I followed:
Download the desired source distribution:
MySQL 5.1 Source Downloads
As root, create any necessary directories, transfer to server, gunzip, and extract:
Make the necessary directories for the installation, initially all owned by root:
Configure and make from the source's base directory; I normally place the configure and make steps into a script:
Modify the values to suit your environment and then execute the script:
Install the MySQL system tables:
Change the ownership of key directories:
Create a custom .cnf file and modify the appropriate values:
Alter the following values:
Start MySQL manually via mysqld_safe to test:
Configure this particular server for automatic startup/shutdown:
The mysql.server script looks for your option file in your $basedir; to support this, move the previously created option file into the $basedir:
Now your service is configured to automatically start and stop with the appropriate system run levels; optionally you can start and stop manually via:
Confirm your installation's variables:
Post Configuration Steps
Drop the test database:
Specify password for the root account and drop the anonymous users:
Create an admin user besides root (I will use the oracle account on my machine):
Optionally, set-up the alternate admin user's Unix environment and test:
Alter appropriate profile with
Create .my.cnf in the $HOME directory
Test connection
At this point, your sandbox should be ready to go...
This posts reviews my preferred way of accomplishing this for MySQL on Linux. The server in question already has a previous 4.X.X version of MySQL. Rather than overwrite this, I would like to install 5.1.23, decoupling it from the existing MySQL installation. In order to do this, I will be compiling MySQL from a source distribution specifying a custom TCP port as well as custom file locations, etc. Following this scheme, you could install many different versions of MySQL on a single server.
Here are the steps I followed:
Download the desired source distribution:
MySQL 5.1 Source Downloads
As root, create any necessary directories, transfer to server, gunzip, and extract:
# mkdir -p /opt/mysql/build
# mv mysql-5.1.23-rc.tar.gz /opt/mysql/build/.
# cd /opt/mysql/build/.
# gunzip mysql-5.1.23-rc.tar.gz
# tar -xvf mysql-5.1.23-rc.tar
Make the necessary directories for the installation, initially all owned by root:
mkdir -p /opt/mysql/datafiles
mkdir -p /opt/mysql/5.1.23
mkdir -p /opt/mysql/5.1.23/socket
mkdir -p /opt/mysql/5.1.23/logs
mkdir -p /opt/mysql/5.1.23/pid
Configure and make from the source's base directory; I normally place the configure and make steps into a script:
# cd /opt/mysql/build/5.1.23
# more ./build_mysql.bsh
#!/bin/bash
MYSQL_BASEDIR=/opt/mysql/5.1.23
MYSQL_TCP_PORT=3307
MYSQL_SOCKET_FILE=$MYSQL_BASEDIR/socket/mysql-5123.sock
CFLAGS="-O3 -march=pentiumpro" CXX=gcc CXXFLAGS="-O3 -march=pentiumpro \
-felide-constructors -fno-exceptions -fno-rtti"
./configure \
--prefix=$MYSQL_BASEDIR --enable-assembler \
--with-mysqld-ldflags=-all-static --with-tcp-port=$MYSQL_TCP_PORT \
--with-unix-socket-path=$MYSQL_SOCKET_FILE
make
make install
Modify the values to suit your environment and then execute the script:
./build_mysql.bsh > build_mysql.log
Install the MySQL system tables:
# cd /opt/mysql/5.1.23/bin
# ./mysql_install_db --user=mysql --datadir=/opt/mysql/datafiles
Change the ownership of key directories:
# chown -R mysql:mysql /opt/mysql/5.1.23/logs
# chown -R mysql:mysql /opt/mysql/5.1.23/socket
# chown -R mysql:mysql /opt/mysql/5.1.23/pid
# chown -R mysql:mysql /opt/mysql/datafiles
Create a custom .cnf file and modify the appropriate values:
# cp /opt/mysql/build/mysql-5.1.23-rc/support-files/my-medium.cnf /etc/mysql-5.1.23.cnf
Alter the following values:
pid-file = /opt/mysql/5.1.23/pid/mysqld-5123.pid
datadir = /opt/mysql/datafiles
log = /opt/mysql/5.1.23/logs/rac2.5123.log
log-error = /opt/mysql/5.1.23/logs/rac2.5123.err
basedir = /opt/mysql/5.1.23
Start MySQL manually via mysqld_safe to test:
# cd /opt/mysql/5.1.23
# ./bin/mysqld_safe --defaults-file=/etc/mysql-5.1.23.cnf --user=mysql &
Configure this particular server for automatic startup/shutdown:
# cp /opt/mysql/build/mysql-5.1.23-rc/support-files/mysql.server /etc/init.d/mysql-5.1.23
# chown o+x /etc/init.d/mysql-5.1.23
# chkconfig --add mysql-5.1.23
# chkconfig --level 345 mysql-5.1.23 on
The mysql.server script looks for your option file in your $basedir; to support this, move the previously created option file into the $basedir:
# mv /etc/mysql-5.1.23.cnf /opt/mysql/5.1.23/my.cnf
Now your service is configured to automatically start and stop with the appropriate system run levels; optionally you can start and stop manually via:
# service mysql-5.1.23 start
# service mysql-5.1.23 stop
Confirm your installation's variables:
# /opt/mysql/5.1.23/bin/mysqladmin variables
+---------------------------------+--------------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /opt/mysql/5.1.23/ |
| big_tables | OFF |
| binlog_cache_size | 32768 |
| binlog_format | MIXED |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /opt/mysql/5.1.23/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 10 |
| datadir | /opt/mysql/datafiles/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| engine_condition_pushdown | ON |
| error_count | 0 |
| event_scheduler | OFF |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| foreign_key_checks | ON |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| general_log | ON |
| general_log_file | /opt/mysql/5.1.23/logs/rac2.5123.log |
| group_concat_max_len | 1024 |
| have_compress | YES |
| have_crypt | YES |
| have_csv | YES |
| have_dynamic_loading | NO |
| have_geometry | YES |
| have_innodb | NO |
| have_ndbcluster | NO |
| have_openssl | NO |
| have_partitioning | NO |
| have_query_cache | YES |
| have_rtree_keys | YES |
| have_ssl | NO |
| have_symlink | YES |
| hostname | rac2.colestock.test |
| identity | 0 |
| init_connect | |
| init_file | |
| init_slave | |
| insert_id | 0 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| keep_files_on_create | OFF |
| key_buffer_size | 16777216 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /opt/mysql/5.1.23/share/mysql/english/ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| last_insert_id | 0 |
| lc_time_names | en_US |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | ON |
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_bin_trust_routine_creators | OFF |
| log_error | /opt/mysql/5.1.23/logs/rac2.5123.err |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| long_query_time | 10.000000 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 151 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| min_examined_row_limit | 0 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| myisam_use_mmap | OFF |
| net_buffer_length | 8192 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old | OFF |
| old_alter_table | OFF |
| old_passwords | ON |
| open_files_limit | 1024 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /opt/mysql/5.1.23/pid/mysqld-5123.pid |
| plugin_dir | /opt/mysql/5.1.23/lib/mysql |
| port | 3307 |
| preload_buffer_size | 32768 |
| protocol_version | 10 |
| pseudo_thread_id | 0 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| rand_seed1 | |
| rand_seed2 | |
| range_alloc_block_size | 4096 |
| read_buffer_size | 262144 |
| read_only | OFF |
| read_rnd_buffer_size | 524288 |
| relay_log | |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| secure_file_priv | |
| server_id | 1 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_compressed_protocol | OFF |
| slave_load_tmpdir | /tmp |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /opt/mysql/5.1.23/pid/mysqld-5123-slow.log |
| socket | /opt/mysql/5.1.23/socket/mysql-5123.sock |
| sort_buffer_size | 524288 |
| sql_auto_is_null | ON |
| sql_big_selects | ON |
| sql_big_tables | OFF |
| sql_buffer_result | OFF |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sql_log_update | ON |
| sql_low_priority_updates | OFF |
| sql_max_join_size | 4294967295 |
| sql_mode | |
| sql_notes | ON |
| sql_quote_show_create | ON |
| sql_safe_updates | OFF |
| sql_select_limit | 4294967295 |
| sql_slave_skip_counter | |
| sql_warnings | OFF |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| system_time_zone | MST |
| table_definition_cache | 128 |
| table_lock_wait_timeout | 50 |
| table_open_cache | 64 |
| table_type | MyISAM |
| thread_cache_size | 0 |
| thread_handling | one-thread-per-connection |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1203539498 |
| tmp_table_size | 16777216 |
| tmpdir | /tmp |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| unique_checks | ON |
| updatable_views_with_limit | YES |
| version | 5.1.23-rc-log |
| version_comment | Source distribution |
| version_compile_machine | i686 |
| version_compile_os | redhat-linux-gnu |
| wait_timeout | 28800 |
| warning_count | 0 |
+---------------------------------+--------------------------------------------+
Post Configuration Steps
Drop the test database:
# ./mysqladmin -u root drop test
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.
Do you really want to drop the 'test' database [y/N] y
Database "test" dropped
Specify password for the root account and drop the anonymous users:
# ./mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.23-rc-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('password');
Query OK, 0 rows affected (0.00 sec)
mysql> SET PASSWORD FOR 'root'@'rac2.colestock.test' = PASSWORD('password');
Query OK, 0 rows affected (0.00 sec)
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('password');
Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM mysql.user WHERE User='';
Query OK, 2 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT HOST, User, Password from mysql.user;
+---------------------+------+------------------+
| HOST | User | Password |
+---------------------+------+------------------+
| localhost | root | 5d2e19393cc5ef67 |
| rac2.colestock.test | root | 5d2e19393cc5ef67 |
| 127.0.0.1 | root | 5d2e19393cc5ef67 |
+---------------------+------+------------------+
3 rows in set (0.00 sec)
Create an admin user besides root (I will use the oracle account on my machine):
mysql> CREATE USER 'oracle'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT User, Host, Password from mysql.user;
+--------+---------------------+------------------+
| User | Host | Password |
+--------+---------------------+------------------+
| root | localhost | 5d2e19393cc5ef67 |
| root | rac2.colestock.test | 5d2e19393cc5ef67 |
| root | 127.0.0.1 | 5d2e19393cc5ef67 |
| oracle | localhost | 5d2e19393cc5ef67 |
+--------+---------------------+------------------+
4 rows in set (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'oracle'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT RELOAD, PROCESS ON *.* TO 'oracle'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
Optionally, set-up the alternate admin user's Unix environment and test:
Alter appropriate profile with
PATH=/opt/mysql/5.1.23/bin:$PATH:$HOME/bin
Create .my.cnf in the $HOME directory
# more ~/.my.cnf
[client]
host = localhost
database = mysql
user = oracle
password = password
protocol = tcp
port = 3307
socket = /opt/mysql/5.1.23/socket/mysql-5123.sock
Test connection
# which mysql
/opt/mysql/5.1.23/bin/mysql
# mysql
mysql> status
--------------
mysql Ver 14.14 Distrib 5.1.23-rc, for redhat-linux-gnu (i686) using EditLine wrapper
Connection id: 51
Current database: mysql
Current user: oracle@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.1.23-rc-log Source distribution
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
TCP port: 3307
Uptime: 1 hour 10 min 27 sec
Threads: 1 Questions: 104 Slow queries: 0 Opens: 29 Flush tables: 1 Open tables: 23 Queries per second avg: 0.24
--------------
At this point, your sandbox should be ready to go...
Labels: Installation, MySQL
_507.png)
Subscribe
