部署mysql多实例

21321321

1.编译安装cmake

1 2 3 4

# tar zxf cmake-3.5.2.tar.gz # ./configure # gmake # gmake install

3.编译安装MySQl

1 2 3 4 5

#useradd mysql -s /sbin/nologin -M # yum install ncurses-devel -y # yum install libaio-devel -y # tar xvf mysql-5.5.49.tar.gz # cd mysql-5.5.49

1 2 3 4 5 6 7 8 9 10 11 12 13 14

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.5.49 \ -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \ -DENABLED_LOCAL_INFILE=ON \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_FEDERATED_STORAGE_ENGINE=1 \ -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \ -DWITHOUT_PARTITION_STORAGE_ENGINE=1 \ -DWITH_FAST_MUTEXES=1 \ -DWITH_ZLIB=bundled \ -DENABLED_LOCAL_INFILE=1 \ -DWITH_READLINE=1 \ -DWITH_EMBEDDED_SERVER=1 \ -DWITH_DEBUG=0

1 2 3 4 5 6 7

# make # make install # ln -s /usr/local/mysql-5.5.49 /usr/local/mysql # echo 'export PATH=/usr/local/mysql/bin/:$PATH' >>/etc/profile # tail -l /etc/profile # source /etc/profile # echo $PATH

4.建立多实例数据目录
//以两个实例为例

1 2 3 4 5 6 7

# mkdir -p /data/{3306,3307}/data # tree /data/ /data/ |-- 3306 |   `-- data `-- 3307     `-- data

5.设置第一个实例配置文件

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65

# vi /data/3306/my.cnf [client] port = 3306 socket = /data/3306/mysql.sock [mysql] no-auto-rehash [mysqld] user = mysql port = 3306 socket = /data/3306/mysql.sock basedir = /usr/local/mysql datadir = /data/3306/data open_files_limit = 1024 back_log = 600 max_connections = 800 max_connect_errors = 3000 table_cache = 614 external-locking = FALSE max_allowed_packet =8M sort_buffer_size = 1M join_buffer_size = 1M thread_cache_size = 100 thread_concurrency = 2 query_cache_size = 2M query_cache_limit = 1M query_cache_min_res_unit = 2k thread_stack = 192K tmp_table_size = 2M max_heap_table_size = 2M long_query_time = 1 pid-file = /data/3306/mysql.pid log-bin = /data/3306/mysql-bin relay-log = /data/3306/relay-bin relay-log-info-file = /data/3306/relay-log.info binlog_cache_size = 1M max_binlog_cache_size = 1M max_binlog_size = 2M expire_logs_days = 7 key_buffer_size = 16M read_buffer_size = 1M read_rnd_buffer_size = 1M bulk_insert_buffer_size = 1M lower_case_table_names = 1 skip-name-resolve slave-skip-errors = 1032,1062 replicate-ignore-db=mysql server-id = 1 innodb_additional_mem_pool_size = 4M innodb_buffer_pool_size = 32M innodb_data_file_path = ibdata1:128M:autoextend innodb_file_io_threads = 4 innodb_thread_concurrency = 8 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 4M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 innodb_file_per_table = 0 [mysqldump] quick max_allowed_packet = 2M [mysqld_safe] log-error=/data/3306/mysql_3306.err pid-file=/data/3306/mysqld.pid

6.设置启动文本

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52

vi /data/3306/mysqld #!/bin/sh port=3306 mysql_user="root" mysql_pwd="" CmdPath="/application/usr/local/mysql/bin" mysql_sock="/data/${port}/mysql.sock" #startup function function_start_mysql() {   if [ ! -e "$mysql_sock" ];then     printf "Starting MySQL...\n"     /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &   else     printf "MySQL is running...\n"     exit   fi } #stop function function_stop_mysql() {   if [ ! -e "$mysql_sock" ];then      printf "MySQL is stopped...\n"      exit   else      printf "Stoping MySQL...\n"      ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown    fi } #restart function function_restart_mysql() {   printf "Restarting MySQL...\n"   function_stop_mysql   sleep 2   function_start_mysql } case $1 in start)   function_start_mysql ;; stop)   function_stop_mysql ;; restart)   function_restart_mysql ;; *)   printf "Usage: /data/${port}/mysql {start|stop|restart}\n" esac # chown -R mysql.mysql /data/ # chmod +x mysqld

7.初始化数据库

1	# /usr/local/mysql/scripts/mysql_install_db  --basedir=/usr/local/mysql/ --datadir=/data/3306/data/ --user=mysql

8.启动数据库

1	# /data/3306/mysqld start

9.登入数据库

1	# mysql -S /data/3306/mysql.sock

10.配置第二个实例

1 2 3 4 5 6 7 8 9 10

# cp /data/3306/my.cnf /data/3307/my.cnf # cp /data/3306/mysqld /data/3307/mysqld //server-id 改为2 server-id = 2 # sed -i 's#3306#3307#g' /data/3307/my.cnf # sed -i 's#3306#3307#g' /data/3307/mysqld # chown -R mysql.mysql /data/ # /usr/local/mysql/scripts/mysql_install_db  --basedir=/usr/local/mysql/ --datadir=/data/3307/data/ --user=mysql # /data/3307/mysqld start # mysql -S /data/3307/mysql.sock