mysql多实例切换_mysql多实例

最新推荐文章于 2021-03-03 20:51:40 发布

weixin_39636057

最新推荐文章于 2021-03-03 20:51:40 发布

阅读量112

点赞数

文章标签： mysql多实例切换

本文链接：https://blog.csdn.net/weixin_39636057/article/details/113225583

版权

准备工作

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.sock4

5 [mysql]6 no-auto-rehash7

8 [mysqld]9 user = mysql

10 port = 3306

11 socket = /data/3306/mysql.sock12 basedir = /application/mysql

13 datadir = /data/3306/data14 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 =8M21 sort_buffer_size =1M22 join_buffer_size =1M23 thread_cache_size = 100

24 thread_concurrency = 2

25 query_cache_size =2M26 query_cache_limit =1M27 query_cache_min_res_unit =2k28 #default_table_type = InnoDB

29 thread_stack =192K30 #transaction_isolation = READ-COMMITTED

31 tmp_table_size =2M32 max_heap_table_size =2M33 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.pid38 log-bin = /data/3306/mysql-bin39 relay-log = /data/3306/relay-bin40 relay-log-info-file = /data/3306/relay-log.info41 binlog_cache_size =1M42 max_binlog_cache_size =1M43 max_binlog_size =2M44 expire_logs_days = 7

45 key_buffer_size =16M46 read_buffer_size =1M47 read_rnd_buffer_size =1M48 bulk_insert_buffer_size =1M49 #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

55 lower_case_table_names = 1

56 skip-name-resolve57 slave-skip-errors = 1032,1062

58 replicate-ignore-db=mysql

60 server-id = 1

62 innodb_additional_mem_pool_size =4M63 innodb_buffer_pool_size =32M64 innodb_data_file_path = ibdata1:128M:autoextend65 innodb_file_io_threads = 4

66 innodb_thread_concurrency = 8

67 innodb_flush_log_at_trx_commit = 2

68 innodb_log_buffer_size =2M69 innodb_log_file_size =4M70 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 quick76 max_allowed_packet =2M77

78 [mysqld_safe]79 log-error=/data/3306/mysql_oldboy3306.err80 pid-file=/data/3306/mysqld.pid

3306-my.cnf

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 ################################################

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"];then20 printf "Starting MySQL...\n"

21 /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &

22 else

23 printf "MySQL is running...\n"

24 exit

25 fi26 }27

28 #stop function

29 function_stop_mysql()30 {31 if [ ! -e "$mysql_sock"];then32 printf "MySQL is stopped...\n"

33 exit

34 else

35 printf "Stoping MySQL...\n"

36 ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown37 fi38 }39

40 #restart function

41 function_restart_mysql()42 {43 printf "Restarting MySQL...\n"

44 function_stop_mysql45 sleep 2

46 function_start_mysql47 }48

49 case $1in50 start)51 function_start_mysql52 ;;53 stop)54 function_stop_mysql55 ;;56 restart)57 function_restart_mysql58 ;;59 *)60 printf "Usage: /data/${port}/mysql {start|stop|restart}\n"

61 esac

3306-mysql

1 [client]2 port = 3307

3 socket = /data/3307/mysql.sock4

5 [mysql]6 no-auto-rehash7

8 [mysqld]9 user = mysql

10 port = 3307

11 socket = /data/3307/mysql.sock12 basedir = /application/mysql

13 datadir = /data/3307/data14 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 =8M21 sort_buffer_size =1M22 join_buffer_size =1M23 thread_cache_size = 100

24 thread_concurrency = 2

25 query_cache_size =2M26 query_cache_limit =1M27 query_cache_min_res_unit =2k28 #default_table_type = InnoDB

29 thread_stack =192K30 #transaction_isolation = READ-COMMITTED

31 tmp_table_size =2M32 max_heap_table_size =2M33 #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.pid38 #log-bin = /data/3307/mysql-bin

39 relay-log = /data/3307/relay-bin40 relay-log-info-file = /data/3307/relay-log.info41 binlog_cache_size =1M42 max_binlog_cache_size =1M43 max_binlog_size =2M44 expire_logs_days = 7

45 key_buffer_size =16M46 read_buffer_size =1M47 read_rnd_buffer_size =1M48 bulk_insert_buffer_size =1M49 #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

55 lower_case_table_names = 1

56 skip-name-resolve57 slave-skip-errors = 1032,1062

58 replicate-ignore-db=mysql

60 server-id = 3

62 innodb_additional_mem_pool_size =4M63 innodb_buffer_pool_size =32M64 innodb_data_file_path = ibdata1:128M:autoextend65 innodb_file_io_threads = 4

66 innodb_thread_concurrency = 8

67 innodb_flush_log_at_trx_commit = 2

68 innodb_log_buffer_size =2M69 innodb_log_file_size =4M70 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 quick76 max_allowed_packet =2M77

78 [mysqld_safe]79 log-error=/data/3307/mysql_oldboy3307.err80 pid-file=/data/3307/mysqld.pid

3307-my.cnf

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 ################################################

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"];then20 printf "Starting MySQL...\n"

21 /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &

22 else

23 printf "MySQL is running...\n"

24 exit

25 fi26 }27

28 #stop function

29 function_stop_mysql()30 {31 if [ ! -e "$mysql_sock"];then32 printf "MySQL is stopped...\n"

33 exit

34 else

35 printf "Stoping MySQL...\n"

36 ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown37 fi38 }39

40 #restart function

41 function_restart_mysql()42 {43 printf "Restarting MySQL...\n"

44 function_stop_mysql45 sleep 2

46 function_start_mysql47 }48

49 case $1in50 start)51 function_start_mysql52 ;;53 stop)54 function_stop_mysql55 ;;56 restart)57 function_restart_mysql58 ;;59 *)60 printf "Usage: /data/${port}/mysql {start|stop|restart}\n"

61 esac

3307-mysql

1 [client]2 port = 3308

3 socket = /data/3308/mysql.sock4

5 [mysql]6 no-auto-rehash7

8 [mysqld]9 user = mysql

10 port = 3308

11 socket = /data/3308/mysql.sock12 basedir = /application/mysql

13 datadir = /data/3308/data14 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 =8M21 sort_buffer_size =1M22 join_buffer_size =1M23 thread_cache_size = 100

24 thread_concurrency = 2

25 query_cache_size =2M26 query_cache_limit =1M27 query_cache_min_res_unit =2k28 #default_table_type = InnoDB

29 thread_stack =192K30 #transaction_isolation = READ-COMMITTED

31 tmp_table_size =2M32 max_heap_table_size =2M33 #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.pid38 #log-bin = /data/3308/mysql-bin

39 relay-log = /data/3308/relay-bin40 relay-log-info-file = /data/3308/relay-log.info41 binlog_cache_size =1M42 max_binlog_cache_size =1M43 max_binlog_size =2M44 expire_logs_days = 7

45 key_buffer_size =16M46 read_buffer_size =1M47 read_rnd_buffer_size =1M48 bulk_insert_buffer_size =1M49 #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

55 lower_case_table_names = 1

56 skip-name-resolve57 slave-skip-errors = 1032,1062

58 replicate-ignore-db=mysql

60 server-id = 5

62 innodb_additional_mem_pool_size =4M63 innodb_buffer_pool_size =32M64 innodb_data_file_path = ibdata1:128M:autoextend65 innodb_file_io_threads = 4

66 innodb_thread_concurrency = 8

67 innodb_flush_log_at_trx_commit = 2

68 innodb_log_buffer_size =2M69 innodb_log_file_size =4M70 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 quick76 max_allowed_packet =2M77

78 [mysqld_safe]79 log-error=/data/3308/mysql_oldboy3307.err80 pid-file=/data/3308/mysqld.pid

3308-my.cnf

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 ################################################

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"];then20 printf "Starting MySQL...\n"

21 /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &

22 else

23 printf "MySQL is running...\n"

24 exit

25 fi26 }27

28 #stop function

29 function_stop_mysql()30 {31 if [ ! -e "$mysql_sock"];then32 printf "MySQL is stopped...\n"

33 exit

34 else

35 printf "Stoping MySQL...\n"

36 ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown37 fi38 }39

40 #restart function

41 function_restart_mysql()42 {43 printf "Restarting MySQL...\n"

44 function_stop_mysql45 sleep 2

46 function_start_mysql47 }48

49 case $1in50 start)51 function_start_mysql52 ;;53 stop)54 function_stop_mysql55 ;;56 restart)57 function_restart_mysql58 ;;59 *)60 printf "Usage: /data/${port}/mysql {start|stop|restart}\n"

61 esac

3308-mysql

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

一个小故障解决：

weixin_39636057

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql多实例切换_mysql多实例

准备工作pkill mysqldps -ef|grep mysqlrm -rf /etc/init.d/mysqldmkdir -p /data/{3306,3307}/datatree /data/1 [client]2 port = 33063 socket = /data/3306/mysql.sock45 [mysql]6 no-auto-rehas...
复制链接

扫一扫