多配置文件部署mysql单机多实例

1.安装gcc-c++、ncurses依赖包

?

1	# yum install gcc-c++  ncurses-devel

2.安装cmake，用来编译mysql

?

1 2 3 4 5 6 7 8

# tar -xvf cmake-3.2.0-.tar.gz # mv cmake-3.2.0 cmake # mv cmake /tmp/ # cd /tmp/cmake/ # ./bootstrap # make # make install # cmake --version

3.安装bison

?

1 2 3 4 5 6 7 8

# tar -xvf bison-3.0.tar.gz # mv bison-3.0 bison # mv bison /tmp # cd /tmp/bison/ # ./cofigure # make # make install # bison --version

4.创建mysql用户

?

1 2	# /usr/sbin/groupadd mysql # useradd -s /sbin/nologin -g  mysql -M mysql

5.编译安装

配置

?

1 2 3 4 5 6 7 8 9 10 11

# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ -DMYSQL_UNIX_ADDR=/tmp/mysqld.sock \ -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci \ -DEXTRA_CHARSETS=all \ -DWITH_EXTRA_CHARSETS:STRING=utf8,gbk \ -DWITH_MYISAM_STORAGE_ENGINE=1 \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DENABLED_LOCAL_INFILE=1 \ -DMYSQL_TCP_PORT=3306 \ -DMYSQL_USER=mysql \

生成可执行文件

?

1

# make

安装

?

1	# make install

6.创建多实例的数据文件目录

?

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

# mkdir -p /data/{3306,3307}/data/ # mkdir -p /data/{3306,3307}/log/ # tree /data /data                            #多实例的根目录 ├── 3306                         #3306实例的根目录 │   ├── data                     #3306实例的数据目录 │   └── log                      #3306实例的日志目录 │   └── tmp                      #3306实例的临时文件目录 └── 3307                         #3307实例的根目录     ├── data                     #3307实例的数据目录     └── log                      #3307实例的日志目录     └── tmp                      #3307实例的临时文件目录

data  - 存放数据文件：数据，索引，二进制日志（bin log），重放日志（replay log）。
log    - 存放日志文件：慢查询日志，错误日志。
tmp  - 存放临时文件：进程ID文件。

7.配置多实例的配置文件
为每个实例建立一个配置文件

+ View Code?

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

# vi /data/3306/my.cnf [client] default-character-set = utf8 port = 3306 socket = /data/3306/tmp/mysql.sock   [mysqld] server-id = 1 collation-server = utf8_unicode_ci init-connect = 'SET NAMES utf8' character-set-server = utf8 port        = 3306 socket      = /data/3306/tmp/mysql.sock datadir     = /data/3306/data/ log-error   = /data/3306/log/mysql.err pid-file     = /data/3306/tmp/mysql.pid log-bin     = /data/3306/data/mysql-bin relay_log     = /data/3306/data/relay-bin slow_query_log_file = /data/3306/log/slowquery.log general_log_file     = /data/3306/log/general.log   skip-external-locking key_buffer_size = 16M max_allowed_packet = 1M table_open_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M binlog_format = mixed slow_query_log = 1 long_query_time = 1 general_log = off   [mysqldump] quick max_allowed_packet = 16M   [mysql] no-auto-rehash   [myisamchk] key_buffer_size = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M   [mysqlhotcopy] interactive-timeout

+ View Code?

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

# vi /data/3307/my.cnf [client] default-character-set = utf8 port = 3307 socket = /data/3307/tmp/mysql.sock   [mysqld] server-id = 2 collation-server = utf8_unicode_ci init-connect = 'SET NAMES utf8' character-set-server = utf8 port        = 3307 socket      = /data/3307/tmp/mysql.sock datadir     = /data/3307/data/ log-error   = /data/3307/log/mysql.err pid-file     = /data/3307/tmp/mysql.pid log-bin     = /data/3307/data/mysql-bin relay_log     = /data/3307/data/relay-bin slow_query_log_file = /data/3307/log/slowquery.log general_log_file     = /data/3307/log/general.log   skip-external-locking key_buffer_size = 16M max_allowed_packet = 1M table_open_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M binlog_format = mixed slow_query_log = 1 long_query_time = 1 general_log = off   [mysqldump] quick max_allowed_packet = 16M   [mysql] no-auto-rehash   [myisamchk] key_buffer_size = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M   [mysqlhotcopy] interactive-timeout

8.配置mysql命令的环境变量

方法一：

?

1	# echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile<br><br># source /etc/profile

方法二：
把/usr/local/mysql/bin/下面的命令拷贝到全局系统命令路径/usr/local/sbin/下，或者做个link

9.初始化数据库,创建基础的数据库文件

?

1 2 3 4 5

# cd /usr/local/mysql/scripts/   #./mysql_install_db --defaults-file=/data/3306/my.cnf --user=mysql --basedir=/usr/local/mysql   #./mysql_install_db --defaults-file=/data/3307/my.cnf --user=mysql --basedir=/usr/local/mysql

10.配置多实例的文件权限
授权mysql用户和组管理多实例目录/data

?

1	# chown -R mysql.mysql /data

11.启动

?

1 2 3

# mysqld_safe --defaults-file=/data/3306/my.cnf 2>&1 > /dev/null &   # mysqld_safe --defaults-file=/data/3307/my.cnf 2>&1 > /dev/null &

12.修改的root用户的密码并且限制只能本机登陆

?

1 2 3

# mysqladmin -S /data/3306/tmp/mysql.sock -u root -h 'localhost' password 'xxxx'   # mysqladmin -S /data/3307/tmp/mysql.sock -u root -h 'localhost' password 'xxxx'

13.使用root用户登陆

?

1 2 3

# mysql -S  /data/3306/tmp/mysql.sock -u root -p   # mysql -S  /data/3307/tmp/mysql.sock -u root -p

14.停止实例

?

1 2 3

# mysqladmin -S /data/3306/tmp/mysql.sock -u root -p shutdown   # mysqladmin -S /data/3307/tmp/mysql.sock -u root -p shutdown