mysql多进程写_Mysql多实例多进程方案实践说明

环境说明

服务器：R310

raid:h700 level: raid 10

磁盘：2T

ip: 192.168.11.174

system:debian6_x64

mysql: 5.5.29

mysql安装方式为编译安装，请参照《Debian 6.0(squeeze) 下编译安装 MySQL 5.5》 或者 运行 lnmp.sh

编译安装之后，

mysql主目录：/usr/local/mysql目录

数据目录：/usr/local/data/mysql/

配置文件：/etc/my.cnf

启动脚本：/etc/init.d/mysqld

注意：确认mysql没启动，如果启动了需要停止mysql

检查/usr/local/data/mysql是否有这三个文件ib_logfile0 ib_logfile1 ibdata1

如果有，请进入/usr/local/data/mysql目录，在删除以下两个文件

-rw-rw---- 1 mysql mysql 5.0M Aug 15 07:29 ib_logfile0

-rw-rw---- 1 mysql mysql 5.0M Aug 15 07:26 ib_logfile1

-rw-rw---- 1 mysql mysql  18M Aug 15 07:29 ibdata1

/usr/local/data/mysql# rm ib_logfile0 ib_logfile1 ibdata1

删除之后，暂时不启动mysql

多实例多进程流程

1、先生成实例mysql2 mysql3

sh /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/usr/local/data/mysql2 --user=mysql

sh /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/usr/local/data/mysql3 --user=mysql

chown -R mysql:mysql /usr/local/data/mysql2

chown -R mysql:mysql /usr/local/data/mysql3

2、修改/etc/my.cnf配置文件

备份

cp /etc/my.cnf /etc/my.cnf.bak

修改如下

vim /etc/my.cnf

[mysqld_multi]

mysqld     = /usr/local/mysql/bin/mysqld_safe

mysqladmin = /usr/local/mysql/bin/mysqladmin

#user       = mysqladmin

#password   = sydbg

log = /usr/local/data/mysql/multi.log

[mysqld1]

port            = 3306

socket          = /tmp/mysql.sock

skip-external-locking

pid-file = /usr/local/data/mysql/mysqld1.pid

datadir = /usr/local/data/mysql

log-error=/usr/local/data/mysql/mysql1.err

key_buffer              = 384M

max_allowed_packet      = 16M

thread_stack            = 256K

thread_cache_size       = 32

sort_buffer_size = 2M

join_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 8M

tmp_table_size = 64M

max_heap_table_size = 4G

myisam-recover         = BACKUP

max_connections        = 500

query_cache_limit       = 1M

query_cache_size        = 32M

#log-bin=mysql-bin

expire_logs_days        = 10

max_binlog_size         = 100M

# binary logging format - mixed recommended

binlog_format=mixed

#log-bin=mysql-bin

expire_logs_days        = 10

max_binlog_size         = 100M

# binary logging format - mixed recommended

binlog_format=mixed

innodb_additional_mem_pool_size = 16M

innodb_autoextend_increment = 8M

innodb_buffer_pool_size = 2G

innodb_checksums = 1

innodb_commit_concurrency = 0

innodb_concurrency_tickets = 500

innodb_doublewrite = 1

innodb_fast_shutdown = 1

innodb_file_io_threads = 4

innodb_file_per_table = 1

innodb_flush_log_at_trx_commit = 0

innodb_flush_method = O_DIRECT

innodb_lock_wait_timeout = 30

innodb_locks_unsafe_for_binlog = 0

innodb_log_buffer_size = 3M

innodb_max_dirty_pages_pct = 80

innodb_open_files = 3000

innodb_data_file_path=ibdata1:50M:autoextend

innodb_log_file_size = 800M

innodb_log_files_in_group = 2

user       = mysql

[mysqld2]

port            = 3307

socket          = /tmp/mysql.sock2

skip-external-locking

pid-file = /usr/local/data/mysql2/mysqld2.pid

datadir = /usr/local/data/mysql2

log-error=/usr/local/data/mysql2/mysql2.err

key_buffer              = 384M

max_allowed_packet      = 16M

thread_stack            = 256K

thread_cache_size       = 32

sort_buffer_size = 2M

join_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 8M

tmp_table_size = 64M

max_heap_table_size = 4G

myisam-recover         = BACKUP

max_connections        = 500

query_cache_limit       = 1M

query_cache_size        = 32M

#log-bin=mysql-bin

expire_logs_days        = 10

max_binlog_size         = 100M

# binary logging format - mixed recommended

binlog_format=mixed

#log-bin=mysql-bin

expire_logs_days        = 10

max_binlog_size         = 100M

# binary logging format - mixed recommended

binlog_format=mixed

innodb_additional_mem_pool_size = 16M

innodb_autoextend_increment = 8M

innodb_buffer_pool_size = 2G

innodb_checksums = 1

innodb_commit_concurrency = 0

innodb_concurrency_tickets = 500

innodb_doublewrite = 1

innodb_fast_shutdown = 1

innodb_file_io_threads = 4

innodb_file_per_table = 1

innodb_flush_log_at_trx_commit = 0

innodb_flush_method = O_DIRECT

innodb_lock_wait_timeout = 30

innodb_locks_unsafe_for_binlog = 0

innodb_log_buffer_size = 3M

innodb_max_dirty_pages_pct = 80

innodb_open_files = 3000

innodb_data_file_path=ibdata1:50M:autoextend

innodb_log_file_size = 800M

innodb_log_files_in_group = 2

user       = mysql

[mysqld3]

port            = 3308

socket          = /tmp/mysql.sock3

skip-external-locking

pid-file = /usr/local/data/mysql3/mysqld3.pid

datadir = /usr/local/data/mysql3

log-error=/usr/local/data/mysql3/mysql3.err

key_buffer              = 384M

max_allowed_packet      = 16M

thread_stack            = 256K

thread_cache_size       = 32

sort_buffer_size = 2M

join_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 8M

tmp_table_size = 64M

max_heap_table_size = 4G

myisam-recover         = BACKUP

max_connections        = 500

query_cache_limit       = 1M

query_cache_size        = 32M

#log-bin=mysql-bin

expire_logs_days        = 10

max_binlog_size         = 100M

# binary logging format - mixed recommended

binlog_format=mixed

#log-bin=mysql-bin

expire_logs_days        = 10

max_binlog_size         = 100M

# binary logging format - mixed recommended

binlog_format=mixed

innodb_additional_mem_pool_size = 16M

innodb_autoextend_increment = 8M

innodb_buffer_pool_size = 2G

innodb_checksums = 1

innodb_commit_concurrency = 0

innodb_concurrency_tickets = 500

innodb_doublewrite = 1

innodb_fast_shutdown = 1

innodb_file_io_threads = 4

innodb_file_per_table = 1

innodb_flush_log_at_trx_commit = 0

innodb_flush_method = O_DIRECT

innodb_lock_wait_timeout = 30

innodb_locks_unsafe_for_binlog = 0

innodb_log_buffer_size = 3M

innodb_max_dirty_pages_pct = 80

innodb_open_files = 3000

innodb_data_file_path=ibdata1:50M:autoextend

innodb_log_file_size = 800M

innodb_log_files_in_group = 2

user       = mysql

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

# Remove the next comment character if you are not familiar with SQL

#safe-updates

[myisamchk]

key_buffer_size = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M

#

# * IMPORTANT: Additional settings that can override those from this file!

#   The files must end with '.cnf', otherwise they'll be ignored.

#!includedir /etc/mysql/conf.d/

3、启动mysql mysql2 mysql3

将多实例脚本拷贝至/etc/init.d/

cp  /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi

开启

/etc/init.d/mysqld_multi start 1-3

查看端口是否监听

root@demohost:/etc# netstat -ano | egrep "3307|3308|3306"

tcp        0      0 0.0.0.0:3307            0.0.0.0:*               LISTEN      off (0.00/0/0)

tcp        0      0 0.0.0.0:3308            0.0.0.0:*               LISTEN      off (0.00/0/0)

tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      off (0.00/0/0)

进入数据库,检查是否正常

mysql -uroot -p -P3306 -h127.0.0.1

mysql -uroot -p -P3307 -h127.0.0.1

mysql -uroot -p -P3308 -h127.0.0.1

停止，有两个方案

方案一

说明：需要在配置文件里面加入一个账户，密码为明文，非常不安全，不推荐

进入数据库,检查是否正常

mysql -uroot -p -P3306 -h127.0.0.1

mysql -uroot -p -P3307 -h127.0.0.1

mysql -uroot -p -P3308 -h127.0.0.1

查看权限表

mysql> select user,host,password from mysql.user;

+------------+-----------+-------------------------------------------+

| user       | host      | password                                  |

+------------+-----------+-------------------------------------------+

| root       | localhost |                                           |

| root       | demohost  |                                           |

| root       | 127.0.0.1 |                                           |

| root       | ::1       |                                           |

|            | localhost |                                           |

|            | demohost  |                                           |

| mysqladmin | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

+------------+-----------+-------------------------------------------+

删除匿名账号

drop user ''@'demohost';

drop user ''@'localhost';

drop user 'mysqladmin'@'127.0.0.1';

查看匿名账号是否正常

mysql> select user,host,password from mysql.user;

+------------+-----------+-------------------------------------------+

| user       | host      | password                                  |

+------------+-----------+-------------------------------------------+

| root       | localhost |                                           |

| root       | demohost  |                                           |

| root       | 127.0.0.1 |                                           |

| root       | ::1       |                                           |

| mysqladmin | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

+------------+-----------+-------------------------------------------+

在三个实例上，都加入拥有停止服务的账号权限，如：

GRANT ALL PRIVILEGES ON *.* TO 'mysqladmin'@'localhost' IDENTIFIED BY '123456';

修改/etc/my.cnf配置

将

[mysqld_multi]

mysqld     = /usr/local/mysql/bin/mysqld_safe

mysqladmin = /usr/local/mysql/bin/mysqladmin

#user       = mysqladmin

#password   = sydbg

log = /usr/local/data/mysql/multi.log

改为

[mysqld_multi]

mysqld     = /usr/local/mysql/bin/mysqld_safe

mysqladmin = /usr/local/mysql/bin/mysqladmin

user       = mysqladmin

password   = sydbg

log = /usr/local/data/mysql/multi.log

停止mysql服务

mysqladmin shutdown -uroot -p -P3306 -h127.0.0.1

mysqladmin shutdown -uroot -p -P3307 -h127.0.0.1

mysqladmin shutdown -uroot -p -P3308 -h127.0.0.1

查看端口是否还被监听

netstat -ano | egrep "3307|3308|3306"

启动服务，让mysql重新加载配置

/etc/init.d/mysqld_multi start 1-3

之后就可以使用如下命令控制进程

/etc/init.d/mysqld_multi stop 1-3

方案二

停止mysql服务

mysqladmin shutdown -uroot -p -P3306 -h127.0.0.1

mysqladmin shutdown -uroot -p -P3307 -h127.0.0.1

mysqladmin shutdown -uroot -p -P3308 -h127.0.0.1