Installing Multiple Versions of MySQL
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:
# 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...
