环境说明 服务器：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