[关闭]
@Zjmainstay 2015-09-25T13:00:02.000000Z 字数 4465 阅读 1316

mysql单机多实例

数据库 配置 MySQL


初始化数据库

  1. sudo mysql_install_db --basedir=/Applications/XAMPP/xamppfiles --datadir=/Applications/XAMPP/xamppfiles/var/mysql3308

数据库目录授权(_mysql参考原有的mysql目录所有者和分组)

  1. sudo chown -R _mysql:_mysql /Applications/XAMPP/xamppfiles/var/mysql3308

创建tmp目录

  1. sudo mkdir /Applications/XAMPP/xamppfiles/temp/mysql3308
  2. sudo chown -R _mysql:_mysql /Applications/XAMPP/xamppfiles/temp/mysql3308

启动脚本

  1. #!/bin/sh
  2. #@filename /Applications/XAMPP/bin/mysqld_multi.server
  3. #@usage sudo /Applications/XAMPP/bin/mysqld_multi.server start 1,2 #注:1对应[mysqld1]
  4. #
  5. # A simple startup script for mysqld_multi by Tim Smith and Jani Tolonen.
  6. # This script assumes that my.cnf file exists either in /etc/my.cnf or
  7. # /root/.my.cnf and has groups [mysqld_multi] and [mysqldN]. See the
  8. # mysqld_multi documentation for detailed instructions.
  9. #
  10. # This script can be used as /etc/init.d/mysql.server
  11. #
  12. # Comments to support chkconfig on RedHat Linux
  13. # chkconfig: 2345 64 36
  14. # description: A very fast and reliable SQL database engine.
  15. #
  16. # Version 1.0
  17. #
  18. basedir=/Applications/XAMPP/xamppfiles
  19. bindir=/Applications/XAMPP/xamppfiles/bin
  20. cnffile=/Applications/XAMPP/xamppfiles/etc/my.cnf
  21. defaultsfile="--defaults-file=$cnffile"
  22. if test -x $bindir/mysqld_multi
  23. then
  24. mysqld_multi="$bindir/mysqld_multi";
  25. else
  26. echo "Can't execute $bindir/mysqld_multi from dir $basedir";
  27. exit;
  28. fi
  29. case "$1" in
  30. 'start' )
  31. "$mysqld_multi" "$defaultsfile" start $2
  32. ;;
  33. 'stop' )
  34. "$mysqld_multi" "$defaultsfile" stop $2
  35. ;;
  36. 'report' )
  37. "$mysqld_multi" "$defaultsfile" report $2
  38. ;;
  39. 'restart' )
  40. "$mysqld_multi" "$defaultsfile" stop $2
  41. "$mysqld_multi" "$defaultsfile" start $2
  42. ;;
  43. *)
  44. echo "Usage: $0 {start|stop|report|restart}" >&2
  45. ;;
  46. esac
  47. 

sudo mysqld_multi --defaults-file=/Applications/XAMPP/xamppfiles/etc/my.cnf start 1

/Applications/XAMPP/xamppfiles/etc/my.cnf

  1. [client]
  2. default-character-set = utf8
  3. [mysqld_multi]
  4. mysqld = /Applications/XAMPP/xamppfiles/bin/mysqld_safe
  5. mysqladmin = /Applications/XAMPP/xamppfiles/bin/mysqladmin
  6. log = /Applications/XAMPP/xamppfiles/logs/mysqld_multi.log
  7. [mysqld6]
  8. user=mysql
  9. port=3306
  10. server-id=3306
  11. socket =/Applications/XAMPP/xamppfiles/var/mysql/mysql.sock
  12. pid-file = /Applications/XAMPP/xamppfiles/var/mysql/Zjmainstay-PC.pid
  13. datadir = /Applications/XAMPP/xamppfiles/var/mysql
  14. innodb_data_home_dir=/Applications/XAMPP/xamppfiles/var/mysql/
  15. innodb_data_file_path=ibdata1:10M:autoextend
  16. innodb_log_group_home_dir=/Applications/XAMPP/xamppfiles/var/mysql/
  17. tmpdir=/Applications/XAMPP/xamppfiles/temp/mysql
  18. [mysqld7]
  19. user=mysql
  20. port=3307
  21. server-id=3307
  22. socket =/Applications/XAMPP/xamppfiles/var/mysql3307/mysql.sock
  23. pid-file = /Applications/XAMPP/xamppfiles/var/mysql3307/Zjmainstay-PC.pid
  24. datadir = /Applications/XAMPP/xamppfiles/var/mysql3307
  25. innodb_data_home_dir=/Applications/XAMPP/xamppfiles/var/mysql3307/
  26. innodb_data_file_path=ibdata1:10M:autoextend
  27. innodb_log_group_home_dir=/Applications/XAMPP/xamppfiles/var/mysql3307/
  28. tmpdir=/Applications/XAMPP/xamppfiles/temp/mysql3307
  29. [mysqld8]
  30. user=mysql
  31. port=3308
  32. server-id=3308
  33. socket =/Applications/XAMPP/xamppfiles/var/mysql3308/mysql.sock
  34. pid-file = /Applications/XAMPP/xamppfiles/var/mysql3308/Zjmainstay-PC.pid
  35. datadir = /Applications/XAMPP/xamppfiles/var/mysql3308
  36. innodb_data_home_dir=/Applications/XAMPP/xamppfiles/var/mysql3308/
  37. innodb_data_file_path=ibdata1:10M:autoextend
  38. innodb_log_group_home_dir=/Applications/XAMPP/xamppfiles/var/mysql3308/
  39. tmpdir=/Applications/XAMPP/xamppfiles/temp/mysql3308
  40. [mysqld]
  41. key_buffer=16M
  42. max_allowed_packet=1M
  43. table_open_cache=64
  44. sort_buffer_size=512K
  45. net_buffer_length=8K
  46. read_buffer_size=256K
  47. read_rnd_buffer_size=512K
  48. myisam_sort_buffer_size=8M
  49. log-bin=mysql-bin
  50. plugin_dir=/Applications/XAMPP/xamppfiles/lib/mysql/plugin/
  51. innodb_buffer_pool_size=16M
  52. innodb_log_file_size=5M
  53. innodb_log_buffer_size=8M
  54. innodb_flush_log_at_trx_commit=1
  55. innodb_lock_wait_timeout=50
  56. [mysqldump]
  57. max_allowed_packet=16M
  58. [mysql]
  59. [isamchk]
  60. key_buffer=20M
  61. sort_buffer_size=20M
  62. read_buffer=2M
  63. write_buffer=2M
  64. [myisamchk]
  65. key_buffer=20M
  66. sort_buffer_size=20M
  67. read_buffer=2M
  68. write_buffer=2M
  69. [mysqlhotcopy]

主从配置

  1. 3307 master
  2. GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1' IDENTIFIED BY 'replroot';
  3.  GRANT select ON *.* TO 'readonly'@'127.0.0.1' IDENTIFIED BY 'readonlyroot'; #供slave客户端使用,避免错误写入
  4. flush privileges;
  5. FLUSH TABLES WITH READ LOCK;
  6. mysqldump -uroot -P 3307 -h 127.0.0.1 -R --add-drop-database --events --all-databases > ~/Desktop/tmp/3307.full.sql
  7. SHOW MASTER STATUS; #需要记录结果,后面使用mysql-bin.000004 406
  8. UNLOCK TABLES;
  9. 3308 slave
  10. mysql -uroot -P 3308 -h 127.0.0.1 < ~/Desktop/tmp/3307.full.sql
  11. CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=3307, MASTER_USER='repl', MASTER_PASSWORD='replroot', MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=406;
  12. show slave status;
  13. start slave;
  14. show slave status #检查Slave_IO_Running:和Slave_SQL_Running:状态 都为yes则配置完成!
添加新批注
在作者公开此批注前,只有你和作者可见。
回复批注