• MySQL多实例安装

    1.安装MySQL需要的依赖的包和编译软件
     
    (1)安装MySQL需要的依赖包
    安装MySQL之前，最好先安装MySQL需要的依赖包，不然后面会出现报错，还得回来安装MySQL的依赖包。
    1. [root@Mysql-server tools]# yum install -y ncurses-devel libaio-devel
    2. [root@Mysql-server tools]# rpm -qa ncurses-devel libaio-devel
    3. ncurses-devel-5.7-4.20090207.el6.x86_64
    4. libaio-devel-0.3.107-10.el6.x86_64
     
    (2)安装编译MySQL需要的软件
    要安装cmake编译软件：
    1. [root@Mysql-server tools]# tar xf cmake-2.8.8.tar.gz
    2. [root@Mysql-server tools]# cd cmake-2.8.8
    3. [root@Mysql-server cmake-2.8.8]#./configure
    4. [root@mysql cmake-2.8.8]# gmake
    5. [root@mysql cmake-2.8.8]# gmake install
    2.开始安装MySQL
    2.1 创建用户mysql
    1. [root@mysql tools]# useradd mysql -s /sbin/nologin -M
    2. [root@mysql tools]# id mysql uid=501(mysql) gid=501(mysql) 组=501(mysql) [root@mysql tools]#
    2.2 解压编译MySQL
    1. tar zxf mysql-5.5.32.tar.gz cd mysql-5.5.32
    2. cmake .-DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32
    3. -DMYSQL_DATADIR=/application/mysql-5.5.32/data
    4. -DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/mysql.sock
    5. -DDEFAULT_CHARSET=utf8
    6. -DDEFAULT_COLLATION=utf8_general_ci
    7. -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii
    8. -DENABLED_LOCAL_INFILE=ON
    9. -DWITH_INNOBASE_STORAGE_ENGINE=1
    10. -DWITH_FEDERATED_STORAGE_ENGINE=1
    11. -DWITH_BLACKHOLE_STORAGE_ENGINE=1
    12. -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1
    13. -DWITHOUT_PARTITION_STORAGE_ENGINE=1
    14. -DWITH_FAST_MUTEXES=1
    15. -DWITH_ZLIB=bundled
    16. -DENABLED_LOCAL_INFILE=1
    17. -DWITH_READLINE=1
    18. -DWITH_EMBEDDED_SERVER=1
    19. -DWITH_DEBUG=0
    21. #-- Build files have been written to: /home/oldboy/tools/mysql-5.5.32
    22. 提示，编译时可配置的选项很多，具体可参考结尾附录或官方文档：
    23. make
    24. #[100%] Built target my_safe_process
    25. make install
    26. ln -s /application/mysql-5.5.32//application/mysql
    27. 如果上述操作未出现错误，则MySQL5.5.32软件cmake方式的安装就算成功了。
    编译过程中出现如下报错，解决方法为：
    1. [root@mysql mysql-5.5.32]# rm CMakeCache.txt ###删除这个文件，重新cmake
     
     
    1. [root@mysql mysql-5.5.32]# mkdir -p /data/{3306,3307}/data
    2. [root@mysql mysql-5.5.32]# tree /data/
    3. /data/
    4. ├──3306
    5. │??└── data ##实例3306的数据文件
    6. ├──3307
    7. │??└── data ##实例3307的数据文件
    8. └── test
    2.3 接着创建配置文件，将data.zip文件上传到/(根)下，并且使用unzip命令解压
    1. [root@mysql /]# unzip data.zip ##上传到根下，加压会替换上面创建的/data目录
    2. Archive: data.zip
    3. inflating: data/3306/my.cnf
    4. inflating: data/3306/mysql
    5. inflating: data/3307/my.cnf
    6. inflating: data/3307/mysql
    7. [root@mysql /]# tree /data
    8. data/ data.zip
    9. [root@mysql /]# tree data
    10. data/ data.zip
    11. [root@mysql /]# tree data/
    12. data/
    13. ├──3306
    14. │??├── data
    15. │??├── my.cnf ##实例的配置文件
    16. │??└── mysql ##实例的启动文件
    17. ├──3307
    18. │??├── data
    19. │??├── my.cnf ##实例的配置文件
    20. │??└── mysql ##实例的启动文件
    21. └── test
    /data/3306/my.cnf配置文件信息
    1. [root@mysql /]# cat /data/3306/my.cnf
    2. [client]
    3. port =3306
    4. socket =/data/3306/mysql.sock
    6. [mysql]
    7. no-auto-rehash
    9. [mysqld]
    10. user = mysql
    11. port =3306
    12. socket =/data/3306/mysql.sock
    13. basedir =/application/mysql
    14. datadir =/data/3306/data
    15. open_files_limit =1024
    16. back_log =600
    17. max_connections =800
    18. max_connect_errors =3000
    19. table_cache =614
    20. external-locking = FALSE
    21. max_allowed_packet =8M
    22. sort_buffer_size =1M
    23. join_buffer_size =1M
    24. thread_cache_size =100
    25. thread_concurrency =2
    26. query_cache_size =2M
    27. query_cache_limit =1M
    28. query_cache_min_res_unit =2k
    29. #default_table_type = InnoDB
    30. thread_stack =192K
    31. #transaction_isolation = READ-COMMITTED
    32. tmp_table_size =2M
    33. max_heap_table_size =2M
    34. long_query_time =1
    35. #log_long_format
    36. #log-error = /data/3306/error.log
    37. #log-slow-queries = /data/3306/slow.log
    38. pid-file =/data/3306/mysql.pid
    39. log-bin =/data/3306/mysql-bin
    40. relay-log =/data/3306/relay-bin
    41. relay-log-info-file =/data/3306/relay-log.info
    42. binlog_cache_size =1M
    43. max_binlog_cache_size =1M
    44. max_binlog_size =2M
    45. expire_logs_days =7
    46. key_buffer_size =16M
    47. read_buffer_size =1M
    48. read_rnd_buffer_size =1M
    49. bulk_insert_buffer_size =1M
    50. #myisam_sort_buffer_size = 1M
    51. #myisam_max_sort_file_size = 10G
    52. #myisam_max_extra_sort_file_size = 10G
    53. #myisam_repair_threads = 1
    54. #myisam_recover
    56. lower_case_table_names =1
    57. skip-name-resolve
    58. slave-skip-errors =1032,1062
    59. replicate-ignore-db=mysql
    61. server-id =1
    63. innodb_additional_mem_pool_size =4M
    64. innodb_buffer_pool_size =32M
    65. innodb_data_file_path = ibdata1:128M:autoextend
    66. innodb_file_io_threads =4
    67. innodb_thread_concurrency =8
    68. innodb_flush_log_at_trx_commit =2
    69. innodb_log_buffer_size =2M
    70. innodb_log_file_size =4M
    71. innodb_log_files_in_group =3
    72. innodb_max_dirty_pages_pct =90
    73. innodb_lock_wait_timeout =120
    74. innodb_file_per_table =0
    75. [mysqldump]
    76. quick
    77. max_allowed_packet =2M
    79. [mysqld_safe]
    80. log-error=/data/3306/mysql_oldboy3306.err
    81. pid-file=/data/3306/mysqld.pid
    /data/3307/my.cnf配置文件参数
    1. [root@mysql /]# cat /data/3307/my.cnf
    2. [client]
    3. port =3307
    4. socket =/data/3307/mysql.sock
    6. [mysql]
    7. no-auto-rehash
    9. [mysqld]
    10. user = mysql
    11. port =3307
    12. socket =/data/3307/mysql.sock
    13. basedir =/application/mysql
    14. datadir =/data/3307/data
    15. open_files_limit =1024
    16. back_log =600
    17. max_connections =800
    18. max_connect_errors =3000
    19. table_cache =614
    20. external-locking = FALSE
    21. max_allowed_packet =8M
    22. sort_buffer_size =1M
    23. join_buffer_size =1M
    24. thread_cache_size =100
    25. thread_concurrency =2
    26. query_cache_size =2M
    27. query_cache_limit =1M
    28. query_cache_min_res_unit =2k
    29. #default_table_type = InnoDB
    30. thread_stack =192K
    31. #transaction_isolation = READ-COMMITTED
    32. tmp_table_size =2M
    33. max_heap_table_size =2M
    34. #long_query_time = 1
    35. #log_long_format
    36. #log-error = /data/3307/error.log
    37. #log-slow-queries = /data/3307/slow.log
    38. pid-file =/data/3307/mysql.pid
    39. #log-bin = /data/3307/mysql-bin
    40. relay-log =/data/3307/relay-bin
    41. relay-log-info-file =/data/3307/relay-log.info
    42. binlog_cache_size =1M
    43. max_binlog_cache_size =1M
    44. max_binlog_size =2M
    45. expire_logs_days =7
    46. key_buffer_size =16M
    47. read_buffer_size =1M
    48. read_rnd_buffer_size =1M
    49. bulk_insert_buffer_size =1M
    50. #myisam_sort_buffer_size = 1M
    51. #myisam_max_sort_file_size = 10G
    52. #myisam_max_extra_sort_file_size = 10G
    53. #myisam_repair_threads = 1
    54. #myisam_recover
    56. lower_case_table_names =1
    57. skip-name-resolve
    58. slave-skip-errors =1032,1062
    59. replicate-ignore-db=mysql
    61. server-id =3
    63. innodb_additional_mem_pool_size =4M
    64. innodb_buffer_pool_size =32M
    65. innodb_data_file_path = ibdata1:128M:autoextend
    66. innodb_file_io_threads =4
    67. innodb_thread_concurrency =8
    68. innodb_flush_log_at_trx_commit =2
    69. innodb_log_buffer_size =2M
    70. innodb_log_file_size =4M
    71. innodb_log_files_in_group =3
    72. innodb_max_dirty_pages_pct =90
    73. innodb_lock_wait_timeout =120
    74. innodb_file_per_table =0
    75. [mysqldump]
    76. quick
    77. max_allowed_packet =2M
    79. [mysqld_safe]
    80. log-error=/data/3307/mysql_oldboy3307.err
    81. pid-file=/data/3307/mysqld.pid
     
    特别说明：
    本文多实例启动文件的启动mysql服务实质命令：
    mysqld_safe --defaults-file=/data /3306/my.cnf 2>&1 /dev/null &
    mysqld_safe --defaults-file=/data /3307/my.cnf 2>&1 /dev/null &
     
    本文多实例启动文件的停止mysql服务实质命令：
    mysqladmin -u root -p123456 -S /data/3306/mysql.sock shutdown
    mysqladmin -u root -p123456 -S /data/3307/mysql.sock shutdown
     
    2.4 修改数据文件/data 权限
    1. [root@mysql /]# chown -R mysql.mysql /data
    2. [root@mysql /]# find /data -type f -name "my.cnf"|xargs ls -l ##将my.cnf文件权限设置成644
    3. -rw-r--r--.1 mysql mysql 189910月292013/data/3306/my.cnf
    4. -rw-r--r--.1 mysql mysql 190110月292013/data/3307/my.cnf
    5. [root@mysql /]# find /data -type f -name "mysql"|xargs ls -l ##由于mysql脚本中保存了登录数据库的密码，需要将文件权限设置成700，只允许root,mysql访问
    6. -rw-r--r--.1 mysql mysql 13077月152013/data/3306/mysql
    7. -rw-r--r--.1 mysql mysql 13077月212013/data/3307/mysql
    8. [root@mysql /]# find /data -type f -name "mysql"|xargs chmod 700
    9. [root@mysql /]# find /data -type f -name "mysql"|xargs ls -l
    10. -rwx------.1 mysql mysql 13077月152013/data/3306/mysql
    11. -rwx------.1 mysql mysql 13077月212013/data/3307/mysql
    2.5 配置MySQL命令全局使用路径
         如果不为MySQL的命令配置全局路径，就无法直接在命令行输入mysql,这样只能用全路径/application/msyq/bin/mysql 这样带着路径输出会比较麻烦
     
    2.5.1 配置MySQL全局路径方法
    1.确认mysql命令所在路径
    1. [root@mysql /]# ll /application/mysql/bin/mysql
    2. -rwxr-xr-x.1 root root 53453564月816:05/application/mysql/bin/mysql
    2. 修改PATH路径
    1. [root@mysql /]# tail -1/etc/profile
    2. export PATH=/application/mysql/bin:$PATH ##添加如下行
    3. [root@mysql /]# source /etc/profile ##让其生效
    3. 最后检查PATH
    1. [root@mysql /]# echo $PATH
    2. /application/mysql/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
    2.6 初始化数据库
    1. cd /application/mysql/scripts/##注意和MySQL 5.1的路径不同，MySQL 5.1 不在mysql/bin下
    2. ./mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data/--user=mysql
    3. ./mysql_install_db --basedir=/application/mysql --datadir=/data/3307/data/--user=mysql
    注意：
    当初始化的过程中出现两个OK时，表示初始化成功

    3. 最后启动mysql
    1. [root@mysql scripts]#/data/3306/mysql start
    2. StartingMySQL...
    3. [root@mysql scripts]#/data/3307/mysql start
    4. StartingMySQL...
    6. 查看是否启动成功
    7. [root@mysql scripts]# netstat -lntup|grep mysql
    8. tcp 000.0.0.0:33060.0.0.0:* LISTEN 4614/mysqld
    9. tcp 000.0.0.0:33070.0.0.0:* LISTEN 5412/mysqld
    4. 登录mysql
    mysql -uroot -p123456 -S /data/3306/mysql.sock
    5. 多实例修改mysql 密码
    1. [root@mysql scripts]# mysqladmin -uroot password 123456 -S /data/3306/mysql.sock
    2. [root@mysql scripts]# mysqladmin -uroot password 123456 -S /data/3307/mysql.sock
    6. 修改配置文件中的mysql连接密码，如果不修改否则在停库的时候会报错
    1. [root@mysql scripts]# sed -i '13 s#oldboy#123456#g'/data/3306/mysql
    2. [root@mysql scripts]# sed -n '13p'/data/3306/mysql
    3. mysql_pwd="123456"
    4. [root@mysql scripts]# sed -i '13 s#oldboy#123456#g' /data/3307/mysql [root@mysql scripts]# sed -n '13p' /data/3307/mysql mysql_pwd="123456" [root@mysql scripts]#
    7.停止mysql数据库
    1. [root@mysql scripts]#/data/3306/mysql stop
    2. StopingMySQL...
    3. [root@mysql scripts]#/data/3307/mysql stop
    4. StopingMySQL...
    5. [root@mysql scripts]#
     
    新增一个多实例库3308，操作命令如下
     





    附件列表
  • 相关阅读:
    PostgreSQL Replication之第四章 设置异步复制（4）
    PostgreSQL Replication之第四章 设置异步复制（3）
    PostgreSQL Replication之第四章 设置异步复制（2）
    PostgreSQL Replication之第四章 设置异步复制（1）
    PostgreSQL Replication之第三章 理解即时恢复（4）
    Hdu3065 病毒侵袭持续中
    poj3974 Palindrome
    poj1204 Word Puzzles
    Hdu2222 Keywords Search
    Codeforce 633.C Spy Syndrome 2
  • 原文地址：https://www.cnblogs.com/hackerer/p/5370109.html
Copyright © 2020-2023  润新知