首页 > 代码库 > Installing Multiple Versions of MySQL

Installing Multiple Versions of MySQL

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...


本文出自 “Permanent” 博客,请务必保留此出处http://ericklee.blog.51cto.com/6941516/1554508

Installing Multiple Versions of MySQL