准备工作
pkill mysqld
ps -ef|grep mysql
rm -rf /etc/init.d/mysqld
mkdir -p /data/{3306,3307}/data
tree /data/
1 [client] 2 port = 3306 3 socket = /data/3306/mysql.sock 4 5 [mysql] 6 no-auto-rehash 7 8 [mysqld] 9 user = mysql 10 port = 3306 11 socket = /data/3306/mysql.sock 12 basedir = /application/mysql 13 datadir = /data/3306/data 14 open_files_limit = 1024 15 back_log = 600 16 max_connections = 800 17 max_connect_errors = 3000 18 table_cache = 614 19 external-locking = FALSE 20 max_allowed_packet =8M 21 sort_buffer_size = 1M 22 join_buffer_size = 1M 23 thread_cache_size = 100 24 thread_concurrency = 2 25 query_cache_size = 2M 26 query_cache_limit = 1M 27 query_cache_min_res_unit = 2k 28 #default_table_type = InnoDB 29 thread_stack = 192K 30 #transaction_isolation = READ-COMMITTED 31 tmp_table_size = 2M 32 max_heap_table_size = 2M 33 long_query_time = 1 34 #log_long_format 35 #log-error = /data/3306/error.log 36 #log-slow-queries = /data/3306/slow.log 37 pid-file = /data/3306/mysql.pid 38 log-bin = /data/3306/mysql-bin 39 relay-log = /data/3306/relay-bin 40 relay-log-info-file = /data/3306/relay-log.info 41 binlog_cache_size = 1M 42 max_binlog_cache_size = 1M 43 max_binlog_size = 2M 44 expire_logs_days = 7 45 key_buffer_size = 16M 46 read_buffer_size = 1M 47 read_rnd_buffer_size = 1M 48 bulk_insert_buffer_size = 1M 49 #myisam_sort_buffer_size = 1M 50 #myisam_max_sort_file_size = 10G 51 #myisam_max_extra_sort_file_size = 10G 52 #myisam_repair_threads = 1 53 #myisam_recover 54 55 lower_case_table_names = 1 56 skip-name-resolve 57 slave-skip-errors = 1032,1062 58 replicate-ignore-db=mysql 59 60 server-id = 1 61 62 innodb_additional_mem_pool_size = 4M 63 innodb_buffer_pool_size = 32M 64 innodb_data_file_path = ibdata1:128M:autoextend 65 innodb_file_io_threads = 4 66 innodb_thread_concurrency = 8 67 innodb_flush_log_at_trx_commit = 2 68 innodb_log_buffer_size = 2M 69 innodb_log_file_size = 4M 70 innodb_log_files_in_group = 3 71 innodb_max_dirty_pages_pct = 90 72 innodb_lock_wait_timeout = 120 73 innodb_file_per_table = 0 74 [mysqldump] 75 quick 76 max_allowed_packet = 2M 77 78 [mysqld_safe] 79 log-error=/data/3306/mysql_oldboy3306.err 80 pid-file=/data/3306/mysqld.pid
1 #!/bin/sh 2 ################################################ 3 #this scripts is created by oldboy at 2007-06-09 4 #oldboy QQ:31333741 5 #site:http://www.etiantian.org 6 #blog:http://oldboy.blog.51cto.com 7 #oldboy trainning QQ group: 208160987 226199307 44246017 8 ################################################ 9 10 #init 11 port=3306 12 mysql_user="root" 13 mysql_pwd="oldboy" 14 CmdPath="/application/mysql/bin" 15 mysql_sock="/data/${port}/mysql.sock" 16 #startup function 17 function_start_mysql() 18 { 19 if [ ! -e "$mysql_sock" ];then 20 printf "Starting MySQL... " 21 /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null & 22 else 23 printf "MySQL is running... " 24 exit 25 fi 26 } 27 28 #stop function 29 function_stop_mysql() 30 { 31 if [ ! -e "$mysql_sock" ];then 32 printf "MySQL is stopped... " 33 exit 34 else 35 printf "Stoping MySQL... " 36 ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown 37 fi 38 } 39 40 #restart function 41 function_restart_mysql() 42 { 43 printf "Restarting MySQL... " 44 function_stop_mysql 45 sleep 2 46 function_start_mysql 47 } 48 49 case $1 in 50 start) 51 function_start_mysql 52 ;; 53 stop) 54 function_stop_mysql 55 ;; 56 restart) 57 function_restart_mysql 58 ;; 59 *) 60 printf "Usage: /data/${port}/mysql {start|stop|restart} " 61 esac
1 [client] 2 port = 3307 3 socket = /data/3307/mysql.sock 4 5 [mysql] 6 no-auto-rehash 7 8 [mysqld] 9 user = mysql 10 port = 3307 11 socket = /data/3307/mysql.sock 12 basedir = /application/mysql 13 datadir = /data/3307/data 14 open_files_limit = 1024 15 back_log = 600 16 max_connections = 800 17 max_connect_errors = 3000 18 table_cache = 614 19 external-locking = FALSE 20 max_allowed_packet =8M 21 sort_buffer_size = 1M 22 join_buffer_size = 1M 23 thread_cache_size = 100 24 thread_concurrency = 2 25 query_cache_size = 2M 26 query_cache_limit = 1M 27 query_cache_min_res_unit = 2k 28 #default_table_type = InnoDB 29 thread_stack = 192K 30 #transaction_isolation = READ-COMMITTED 31 tmp_table_size = 2M 32 max_heap_table_size = 2M 33 #long_query_time = 1 34 #log_long_format 35 #log-error = /data/3307/error.log 36 #log-slow-queries = /data/3307/slow.log 37 pid-file = /data/3307/mysql.pid 38 #log-bin = /data/3307/mysql-bin 39 relay-log = /data/3307/relay-bin 40 relay-log-info-file = /data/3307/relay-log.info 41 binlog_cache_size = 1M 42 max_binlog_cache_size = 1M 43 max_binlog_size = 2M 44 expire_logs_days = 7 45 key_buffer_size = 16M 46 read_buffer_size = 1M 47 read_rnd_buffer_size = 1M 48 bulk_insert_buffer_size = 1M 49 #myisam_sort_buffer_size = 1M 50 #myisam_max_sort_file_size = 10G 51 #myisam_max_extra_sort_file_size = 10G 52 #myisam_repair_threads = 1 53 #myisam_recover 54 55 lower_case_table_names = 1 56 skip-name-resolve 57 slave-skip-errors = 1032,1062 58 replicate-ignore-db=mysql 59 60 server-id = 3 61 62 innodb_additional_mem_pool_size = 4M 63 innodb_buffer_pool_size = 32M 64 innodb_data_file_path = ibdata1:128M:autoextend 65 innodb_file_io_threads = 4 66 innodb_thread_concurrency = 8 67 innodb_flush_log_at_trx_commit = 2 68 innodb_log_buffer_size = 2M 69 innodb_log_file_size = 4M 70 innodb_log_files_in_group = 3 71 innodb_max_dirty_pages_pct = 90 72 innodb_lock_wait_timeout = 120 73 innodb_file_per_table = 0 74 [mysqldump] 75 quick 76 max_allowed_packet = 2M 77 78 [mysqld_safe] 79 log-error=/data/3307/mysql_oldboy3307.err 80 pid-file=/data/3307/mysqld.pid
1 #!/bin/sh 2 ################################################ 3 #this scripts is created by oldboy at 2007-06-09 4 #oldboy QQ:31333741 5 #site:http://www.etiantian.org 6 #blog:http://oldboy.blog.51cto.com 7 #oldboy trainning QQ group: 208160987 226199307 44246017 8 ################################################ 9 10 #init 11 port=3307 12 mysql_user="root" 13 mysql_pwd="oldboy" 14 CmdPath="/application/mysql/bin" 15 mysql_sock="/data/${port}/mysql.sock" 16 #startup function 17 function_start_mysql() 18 { 19 if [ ! -e "$mysql_sock" ];then 20 printf "Starting MySQL... " 21 /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null & 22 else 23 printf "MySQL is running... " 24 exit 25 fi 26 } 27 28 #stop function 29 function_stop_mysql() 30 { 31 if [ ! -e "$mysql_sock" ];then 32 printf "MySQL is stopped... " 33 exit 34 else 35 printf "Stoping MySQL... " 36 ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown 37 fi 38 } 39 40 #restart function 41 function_restart_mysql() 42 { 43 printf "Restarting MySQL... " 44 function_stop_mysql 45 sleep 2 46 function_start_mysql 47 } 48 49 case $1 in 50 start) 51 function_start_mysql 52 ;; 53 stop) 54 function_stop_mysql 55 ;; 56 restart) 57 function_restart_mysql 58 ;; 59 *) 60 printf "Usage: /data/${port}/mysql {start|stop|restart} " 61 esac
1 [client] 2 port = 3308 3 socket = /data/3308/mysql.sock 4 5 [mysql] 6 no-auto-rehash 7 8 [mysqld] 9 user = mysql 10 port = 3308 11 socket = /data/3308/mysql.sock 12 basedir = /application/mysql 13 datadir = /data/3308/data 14 open_files_limit = 1024 15 back_log = 600 16 max_connections = 800 17 max_connect_errors = 3000 18 table_cache = 614 19 external-locking = FALSE 20 max_allowed_packet =8M 21 sort_buffer_size = 1M 22 join_buffer_size = 1M 23 thread_cache_size = 100 24 thread_concurrency = 2 25 query_cache_size = 2M 26 query_cache_limit = 1M 27 query_cache_min_res_unit = 2k 28 #default_table_type = InnoDB 29 thread_stack = 192K 30 #transaction_isolation = READ-COMMITTED 31 tmp_table_size = 2M 32 max_heap_table_size = 2M 33 #long_query_time = 1 34 #log_long_format 35 #log-error = /data/3308/error.log 36 #log-slow-queries = /data/3308/slow.log 37 pid-file = /data/3308/mysql.pid 38 #log-bin = /data/3308/mysql-bin 39 relay-log = /data/3308/relay-bin 40 relay-log-info-file = /data/3308/relay-log.info 41 binlog_cache_size = 1M 42 max_binlog_cache_size = 1M 43 max_binlog_size = 2M 44 expire_logs_days = 7 45 key_buffer_size = 16M 46 read_buffer_size = 1M 47 read_rnd_buffer_size = 1M 48 bulk_insert_buffer_size = 1M 49 #myisam_sort_buffer_size = 1M 50 #myisam_max_sort_file_size = 10G 51 #myisam_max_extra_sort_file_size = 10G 52 #myisam_repair_threads = 1 53 #myisam_recover 54 55 lower_case_table_names = 1 56 skip-name-resolve 57 slave-skip-errors = 1032,1062 58 replicate-ignore-db=mysql 59 60 server-id = 5 61 62 innodb_additional_mem_pool_size = 4M 63 innodb_buffer_pool_size = 32M 64 innodb_data_file_path = ibdata1:128M:autoextend 65 innodb_file_io_threads = 4 66 innodb_thread_concurrency = 8 67 innodb_flush_log_at_trx_commit = 2 68 innodb_log_buffer_size = 2M 69 innodb_log_file_size = 4M 70 innodb_log_files_in_group = 3 71 innodb_max_dirty_pages_pct = 90 72 innodb_lock_wait_timeout = 120 73 innodb_file_per_table = 0 74 [mysqldump] 75 quick 76 max_allowed_packet = 2M 77 78 [mysqld_safe] 79 log-error=/data/3308/mysql_oldboy3307.err 80 pid-file=/data/3308/mysqld.pid
1 #!/bin/sh 2 ################################################ 3 #this scripts is created by oldboy at 2007-06-09 4 #oldboy QQ:31333741 5 #site:http://www.etiantian.org 6 #blog:http://oldboy.blog.51cto.com 7 #oldboy trainning QQ group: 208160987 226199307 44246017 8 ################################################ 9 10 #init 11 port=3308 12 mysql_user="root" 13 mysql_pwd="oldboy" 14 CmdPath="/application/mysql/bin" 15 mysql_sock="/data/${port}/mysql.sock" 16 #startup function 17 function_start_mysql() 18 { 19 if [ ! -e "$mysql_sock" ];then 20 printf "Starting MySQL... " 21 /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null & 22 else 23 printf "MySQL is running... " 24 exit 25 fi 26 } 27 28 #stop function 29 function_stop_mysql() 30 { 31 if [ ! -e "$mysql_sock" ];then 32 printf "MySQL is stopped... " 33 exit 34 else 35 printf "Stoping MySQL... " 36 ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown 37 fi 38 } 39 40 #restart function 41 function_restart_mysql() 42 { 43 printf "Restarting MySQL... " 44 function_stop_mysql 45 sleep 2 46 function_start_mysql 47 } 48 49 case $1 in 50 start) 51 function_start_mysql 52 ;; 53 stop) 54 function_stop_mysql 55 ;; 56 restart) 57 function_restart_mysql 58 ;; 59 *) 60 printf "Usage: /data/${port}/mysql {start|stop|restart} " 61 esac
unzip data.zip /
rm -rf /data.zip
[root@oldboy /]# find /data/ -type f -name "mysql" |xargs ls -l
-rw-r--r--. 1 root root 1307 Jul 15 2013 /data/3306/mysql
-rw-r--r--. 1 root root 1307 Jul 21 2013 /data/3307/mysql
[root@oldboy /]# find /data/ -name mysql
/data/3307/mysql
/data/3306/mysql
多实例的自动和停止
初始化:
/application/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf 2>&1 > /dev/null &
/application/mysql/bin/mysqld_safe --defaults-file=/data/3307/my.cnf 2>&1 > /dev/null &
/application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql /application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/data/3307/data --user=mysql chown -R mysql.mysql /data chmod +x /data/3306/mysql chmod +x /data/3307/mysql [需要复制多实例文件mysqll到目录下] [root@oldboy data]# ./3306/mysql start Starting MySQL... [root@oldboy data]# ./3307/mysql start Starting MySQL... [root@oldboy data]# netstat -lntup |grep 3306 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3339/mysqld [root@oldboy data]# netstat -lntup |grep 3307 tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 4056/mysqld
登陆进去mysql命令
mysql -S /data/3307/mysql.sock
停止会报错，因为停止数据库默认要指定密码 的，所以要先把mysql里面密码改为空
改密码
[root@oldboy 3306]# mysqladmin -uroot -S /data/3306/mysql.sock password 'oldboy123' [root@oldboy 3306]# mysqladmin -uroot -S /data/3307/mysql.sock password 'oldboy123'
然后修改msyql 密码
为了安全 要去更改mysql权限
[root@oldboy data]# find /data -type f -name "mysql" -exec chmod 700 {} ;
[root@oldboy data]# find /data -type f -name "mysql" -exec ls -l {} ;
-rwx------. 1 mysql mysql 1310 Oct 25 23:04 /data/3307/mysql
-rwx------. 1 mysql mysql 1310 Oct 25 23:04 /data/3306/mysql
配置好密码后 再次登录 必须输入密码
mysql -uroot -poldboy123 -S /data/3306/mysql.sock
常见问题：
单实例mysql
启动：
Mysql启动原理
优雅关闭数据库：
多实例msyql登录：
设置密码：
修改密码：
找回丢失的密码：
多实例msyql启动修改丢失root密码方法
1，关闭msyql
killall mysqld
2，启动时候加--skip-grant-tables参数
mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-table &
mysql -uroot -p -S /data/3308/mysql.sock
3,修改密码
update mysql.user set password=PASSWORD('123456') where user='root';
FLUSH PRIVILEGES;
关闭mysql
mysqladmin -uroot -p123456 shutdown -S /data/3308/mysql.sock
重启mysql
/data/3308/mysql start
重新登录mysql
mysql -uroot -p123456 -S /data/3308/mysql.sock
一个小故障解决：
