MYSQL数据库多实例配置步骤详解

多实例是一个比较有单的东西了,小编今天来为各位介绍mysql单多实例的配置方法,希望下文可以帮助到各位朋友.

1. [root@Mysql~]#yuminstallvimgccgcc-c++wgettreelsofncurses-develcmakelibaio*-y#依赖库
2. [root@Mysql~]#/usr/sbin/useraddmysql-s/sbin/nologin-M#mysql用户
3. [root@Mysql~]#cd/byrd/tools/
4. [root@Mysqltools]#wgethttp://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.37.tar.gz
5. [root@Mysqltools]#tarzxfmysql-5.5.37.tar.gz
6. [root@Mysqltools]#cdmysql-5.5.37/
7. [root@Mysqlmysql-5.5.37]#cmake.-DCMAKE_INSTALL_PREFIX=/byrd/server/mysql-5.5.37-DDEFAULT_CHARSET=utf8-DDEFAULT_COLLATION=utf8_general_ci-DENABLED_LOCAL_INFILE=ON-DWITH_READLINE=1-DWITH_PARTITION_STORAGE_ENGINE=1-DWITH_MYISAM_STORAGE_ENGINE=1-DWITH_INNOBASE_STORAGE_ENGINE=1-DWITH_FEDERATED_STORAGE_ENGINE=1-DWITH_BLACKHOLE_STORAGE_ENGINE=1-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1-DWITH_MEMORY_STORAGE_ENGINE=1
8. [root@Mysqlmysql-5.5.37]#make&&makeinstall
9. [root@Mysqlmysql-5.5.37]#ln-s/byrd/server/mysql-5.5.37//usr/local/mysql
10. [root@Mysql~]#cd/usr/local/mysql/
11. [root@Mysql~]#mkdir-p/data/{3306,3307}/data
12. [root@Mysql~]#chown-Rmysql.mysql/data
13. [root@Mysqlmysql-5.5.37]#/usr/local/mysql/scripts/mysql_install_db--basedir=/usr/local/mysql/--datadir=/data/3306/data/--user=mysql
14. [root@Mysqlmysql-5.5.37]#ll/data/3306/data/
15. total12
16. drwx------.2mysqlroot4096Jun1522:09mysql
17. drwx------.2mysqlmysql4096Jun1522:36performance_schema
18. drwx------.2mysqlroot4096Jun1522:09test
19. [root@Mysqlmysql]#/usr/local/mysql/scripts/mysql_install_db--basedir=/usr/local/mysql/--datadir=/data/3307/data/--user=mysql
20. [root@Mysqlmysql-5.5.37]#ll/data/3307/data/
21. total12
22. drwx------.2mysqlroot4096Jun1522:37mysql
23. drwx------.2mysqlmysql4096Jun1522:37performance_schema
24. drwx------.2mysqlroot4096Jun1522:37test
25. [root@Mysqlmysql-5.5.37]#cp/byrd/tools/mysql-5.5.37/support-files/my-medium.cnf/data/3307/my.cnf
26. [root@Mysqlmysql-5.5.37]#cp/byrd/tools/mysql-5.5.37/support-files/my-medium.cnf/data/3306/my.cnf
27. [root@Mysqlmysql-5.5.37]#sed-i's#/tmp/mysql.sock#/data/3306/mysql.sock#g'/data/3306/my.cnf
28. [root@Mysqlmysql-5.5.37]#sed-i's#3306#3307#g'/data/3307/my.cnf
29. [root@Mysqlmysql-5.5.37]#sed-i's#=1#=2#g'/data/3307/my.cnf
30. [root@Mysql~]#cat>>/data/3306/my.cnf<<BYRD
31. >pid-file=/data/3306/mysql.pid
32. >log-bin=/data/3306/mysql-bin
33. >relay-log=/data/3306/relay-bin
34. >relay-log-info-file=/data/3306/relay-log.info
35. >log-error=/data/3306/mysql_3306.err
36. >BYRD
37. [root@Mysql~]#cat>>/data/3307/my.cnf<<BYRD
38. >[mysqld_safe]
39. >pid-file=/data/3307/mysql.pid
40. >log-bin=/data/3307/mysql-bin
41. >relay-log=/data/3307/relay-bin
42. >relay-log-info-file=/data/3307/relay-log.info
43. >log-error=/data/3307/mysql_3307.err
44. >BYRD
45. [root@Mysqlmysql]#/usr/local/mysql/bin/mysqld_safe--defaults-file=/data/3306/my.cnf2>&1>/dev/null&--phpfensi.com
46. [root@Mysqlmysql]#/usr/local/mysql/bin/mysqld_safe--defaults-file=/data/3307/my.cnf2>&1>/dev/null&
47. [root@Mysql3307]#netstat-tunlp
48. ActiveInternetconnections(onlyservers)
49. ProtoRecv-QSend-QLocalAddressForeignAddressStatePID/Programname
50. tcp00:::3307:::*LISTEN8046/mysqld
51. tcp00:::3306:::*LISTEN4943/mysqld
52. [root@Mysql3307]#

my.cnf配置文件内容:

1. [client]
2. port=3306
3. socket=/data/3306/mysql.sock
4. [mysql]
5. no-auto-rehash
6. [mysqld]
7. user=mysql
8. port=3306
9. socket=/data/3306/mysql.sock
10. basedir=/usr/local/mysql
11. datadir=/data/3306/data
12. open_files_limit=1024
13. back_log=600
14. max_connections=800
15. max_connect_errors=3000
16. external-locking=FALSE
17. max_allowed_packet=8M
18. sort_buffer_size=1M
19. join_buffer_size=1M
20. thread_cache_size=100
21. thread_concurrency=2
22. query_cache_size=2M
23. query_cache_limit=1M
24. query_cache_min_res_unit=2k
25. thread_stack=192K
26. tmp_table_size=2M
27. max_heap_table_size=2M
28. long_query_time=1
29. pid-file=/data/3306/mysql.pid
30. log-bin=/data/3306/mysql-bin
31. relay-log=/data/3306/relay-bin
32. relay-log-info-file=/data/3306/relay-log.info
33. binlog_cache_size=1M
34. max_binlog_cache_size=1M
35. max_binlog_size=2M
36. expire_logs_days=7
37. key_buffer_size=16M
38. read_buffer_size=1M
39. read_rnd_buffer_size=1M
40. bulk_insert_buffer_size=1M
41. lower_case_table_names=1
42. skip-name-resolve
43. slave-skip-errors=1032,1062
44. replicate-ignore-db=mysql
45. server-id=1
46. [mysqldump]
47. quick
48. max_allowed_packet=2M
49. [mysqld_safe]
50. log-error=/data/3306/mysql_oldboy3306.err
51. pid-file=/data/3306/mysqld.pid

测试登陆:

1. [root@Mysql3306]#/usr/local/mysql/bin/mysql-S/data/3306/mysql.sock
2. mysql>createdatabased3306;
3. QueryOK,1rowaffected(0.53sec)
4. mysql>showdatabases;
5. +--------------------+
6. |Database|
7. +--------------------+
8. |information_schema|
9. |d3306|
10. |mysql|
11. |performance_schema|
12. |test|
13. +--------------------+
14. 5rowsinset(0.13sec)
15. [root@Mysql3306]#/usr/local/mysql/bin/mysql-S/data/3306/mysql.sock
16. mysql>showdatabases;
17. +--------------------+
18. |Database|
19. +--------------------+
20. |information_schema|
21. |mysql|
22. |performance_schema|
23. |test|
24. +--------------------+
25. 4rowsinset(0.04sec)