Linux下Mysql如何安置多实例和主从配置
发布时间：2021-12-24 12:53:24 所属栏目：MySql教程 来源：互联网
导读：小编给大家分享一下Linux下Mysql如何安装多实例和主从配置，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！ mysql创建多个实例，其实就是把DATA文件，SOCK,PORT指向不同的文件和端口 Linux下搭建MySQL多实例环境 1.安装cmake [root@mysql
|
小编给大家分享一下Linux下Mysql如何安装多实例和主从配置，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！ mysql创建多个实例，其实就是把DATA文件，SOCK,PORT指向不同的文件和端口 Linux下搭建MySQL多实例环境 1.安装cmake [root@mysql local]# yum -y install ncurses-devel gcc-c++ [root@mysql local]# cd /usr/local [root@mysql local]# tar zxvf cmake-2.8.4.tar.gz [root@mysql local]# cd cmake-2.8.4 [root@mysql cmake-2.8.4]# ./bootstrap --出现如下报错，缺少c++编译器 [root@mysql local]# yum install gcc-c++ --可处理下面问题 [root@mysql cmake-2.8.4]# gmake [root@mysql cmake-2.8.4]# make install [root@mysql cmake-2.8.4]# /usr/local 2.安装bison [root@mysql local]# tar -zxvf bison-2.5.tar.gz [root@mysql local]# cd bison-2.5 [root@mysql bison-2.5 ]# ./configure [root@mysql bison-2.5 ]# make [root@mysql bison-2.5 ]# make install [root@mysql bison-2.5 ]# /usr/local 3.编译安装mysql包 [root@localhost workspace]# tar xvf mysql-5.5.32.tar.gz [root@localhost workspace]# cd mysql-5.5.32 安装第一个MySQL数据库 （1）创建所需要的文件目录 [root@localhost mysql]# useradd mysql [root@localhost local]# cd /usr/local/ [root@localhost local]# mkdir mysql [root@localhost local]# cd mysql/ [root@localhost mysql]# mkdir data [root@localhost mysql]# mkdir etc [root@localhost mysql]# mkdir log [root@localhost mysql]# mkdir /var/log/mysql [root@localhost mysql]# mkdir /var/run/mysqld [root@localhost mysql]# chown /var/run/mysqld -R [root@localhost mysql]# chown mysql.mysql /var/log/mysql -R [root@mysql mysql]# chown mysql.mysql /usr/local/mysql -R [root@mysql mysql]# chmod +x /usr/local/mysql -R （2）配置MySQL源码编译选项 [root@localhost mysql-5.5.32]#cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DSYSCONFDIR=/usr/local/mysql/etc -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DMYSQL_UNIX_ADDR=/tmp/mysqld.sock -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci （3）编译安装 [root@localhost mysql-5.5.32]# make & make install （4）配置第一个MySQL实例 [root@localhost mysql-5.5.32]# cd /usr/local/mysql [root@localhost mysql]# cp support-files/my-medium.cnf /usr/local/mysql/etc/my.cnf [root@localhost mysql]# vi /usr/local/mysql/etc/my.cnf [client] #password = your_password port = 3306 socket = /usr/local/mysql/mysqld.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3306 socket = /usr/local/mysql/mysqld.sock skip-external-locking key_buffer_size = 16M max_allowed_packet = 1M table_open_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M datadir=/usr/local/mysql/data default-storage-engine=Innodb slow-query-log-file=/usr/local/mysql/log/slow.log log-error=/usr/local/mysql/log/err.log pid-file=/usr/local/mysql/mysql3306.pid server_id=1 character_set_server = utf8 wait-timeout=30 max_connections = 512 log-bin =/usr/local/mysql/log/binlog sync_binlog=1 slow-query-log=1 long-query-time=1 general-log=1 #general-log-file=/data/mysql/log/dml.log lower_case_table_names=1 log_bin_trust_function_creators=1 skip-slave-start binlog-ignore-db=mysql binlog-ignore-db=information_schema binlog-ignore-db=performance_schema replicate_ignore_db=mysql replicate_ignore_db=information_schema replicate_ignore_db=performance_schema expire-logs-days=10 [root@localhost mysql]# cd /usr/local/mysql/scripts/ [root@localhost scripts]# ./mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data & [root@localhost bin]# cd /usr/local/mysql/bin [root@localhost bin]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/etc/my.cnf & --安全模式启动 [1] 28869 [root@localhost bin]# 131016 20:07:13 mysqld_safe Logging to '/usr/local/mysql/data/localhost.localdomain.err'. 131016 20:07:14 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data 查看服务是否启动成功 [root@localhost bin]# netstat -tlnap | grep mysql tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 29147/mysqld [root@localhost bin]#ps -ef | grep mysql 如若启动之后的环境变量与配置文件my.cnf的指定量不同，则查看/etc/下是否有my.cnf文件存在干扰设置的生效，将/etc/my.cnf删除，重启数据库再次查看指定量是否生效。 登录MySQL并修改root用户密码 [root@localhost bin]# ./mysqladmin -uroot password 'newpasswd' [root@localhost bin]# ./mysql -uroot -pnewpasswd Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.5.32-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> exit 安装第二个MySQL数据库 （1）清理配置信息 [root@localhost bin]# cd /usr/local/mysql-5.5.32 [root@localhost mysql-5.5.32]# make clean [root@localhost mysql-5.5.32]# rm -rf CMakeCache.txt （2）创建所需要的文件目录 [root@localhost mysql-5.5.32]# cd /usr/local/ [root@localhost local]# mkdir mysql3307 [root@localhost local]# cd mysql3307/ [root@localhost mysql3307]# mkdir data [root@localhost mysql3307]# mkdir etc [root@localhost mysql3307]# mkdir log [root@localhost mysql3307]# chown mysql.mysql /usr/local/mysql3307 -R （3）配置第二个实例的编译信息 [root@localhost bin]# cd /usr/local/mysql-5.5.32 [root@mysql mysql-5.5.17]# rm -rf CMakeCache.txt [root@localhost mysql-5.5.32]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql3307 -DMYSQL_DATADIR=/usr/local/mysql3307/data -DSYSCONFDIR=/usr/local/mysql3307/etc -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DMYSQL_UNIX_ADDR=/tmp/mysqld3307.sock -DMYSQL_TCP_PORT=3307 -DENABLED_LOCAL_INFILE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci （4）编译安装 [root@localhost mysql-5.5.32]# make & make install （5）配置第二个MySQL实例 [root@localhost mysql-5.5.32]# cd /usr/local/mysql3307 [root@localhost mysql3307]# cp support-files/my-medium.cnf /usr/local/mysql3307/etc/my.cnf [root@localhost mysql3307]# vi /usr/local/mysql3307/etc/my.cnf [client] port = 3307 socket = /usr/local/mysql3307/mysqld3307.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3307 socket = /usr/local/mysql3307/mysqld3307.sock skip-external-locking key_buffer_size = 16M max_allowed_packet = 1M table_open_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M datadir=/usr/local/mysql3307/data default-storage-engine=Innodb slow-query-log-file=/usr/local/mysql3307/log/slow.log log-error=/usr/local/mysql3307/log/err.log [root@localhost mysql3307]# cd /usr/local/mysql3307/scripts/ [root@localhost scripts]# ./mysql_install_db --user=mysql --basedir=/usr/local/mysql3307 --datadir=/usr/local/mysql3307/data & [root@localhost scripts]# cd /usr/local/mysql3307/bin [root@localhost bin]# /usr/local/mysql3307/bin/mysqld_safe --defaults-file=/usr/local/mysql3307/etc/my.cnf & 131016 20:40:27 mysqld_safe Logging to '/usr/local/mysql3307/data/localhost.localdomain.err'. 131016 20:40:27 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql3307/data 查看服务是否启动成功 [root@localhost bin]# netstat -tlnap | grep mysql tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 29147/mysqld tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 7447/mysqld 登录MySQL并修改root用户密码 [root@localhost bin]# ./mysqladmin -uroot password 'eisoo.com123' [root@localhost bin]# ./mysql -uroot -peisoo.com123 Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.5.32-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> exit 增加系统环境变量 [root@localhost /]# vi /etc/profile 增加一行：export PATH=/usr/local/mysql/bin:/usr/local/mysql3307/bin:$PATH [root@localhost /]# source /etc/profile [root@localhost /]# export $PATH 分别启动不同实例： [root@localhost /]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/etc/my.cnf & [root@localhost /]# /usr/local/mysql3307/bin/mysqld_safe --defaults-file=/usr/local/mysql3307/etc/my.cnf & 分别登陆不同实例： [root@localhost /]# mysql -uroot -pnewpasswd -S /tmp/mysqld.sock [root@localhost /]# mysql -uroot -pnewpasswd -S /tmp/mysqld3307.sock 启动还是太麻烦，可以这样做： [root@localhost /]# ln -s /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql3306 [root@localhost /]# ln -s /usr/local/mysql3307/support-files/mysql.server /etc/init.d/mysql3307 给mysql用户添加权限： [root@localhost /]# chmod -R 755 /usr/local/mysql/data [root@localhost /]# chmod -R 755 /usr/local/mysql3307/data 分别启动实例对应的服务： [root@localhost tmp]# service mysql3306 start Starting MySQL. [确定] [root@localhost tmp]# service mysql3307 start Starting MySQL. [确定] 查看服务： [root@localhost tmp]# netstat -tlnap | grep mysql tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 10759/mysqld tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 11097/mysqld 将服务添加到系统服务中，使其开机自动启动。 [root@localhost /]# chkconfig --add mysql3306 [root@localhost /]# chkconfig --add mysql3307 多实例的平滑关闭： [root@localhost /]# mysqladmin -u root -p -S --socket=/usr/local/mysql/mysqld3306.sock shutdown [root@localhost /]# mysqladmin -u root -p -S --socket=/usr/local/mysql3307/mysqld3307.sock shutdown 二．mysql主从配置 1．修改master数据库的my.cnf文件 # vi /etc/my.cnf [mysqld] basedir =/data/mysql datadir =/data/mysql/data port =3306 server_id =1 socket = /data/mysql/mysql.sock pid-file=/data/mysql/mysql.pid character_set_server = utf8 wait-timeout=30 max_connections = 512 default-storage-engine = Innodb log-bin =/data/mysql/log/binlog sync_binlog=1 slow-query-log=1 long-query-time=1 slow-query-log-file=/data/mysql/log/slow.log log-error=/data/mysql/log/err.log general-log=1 general-log-file=/data/mysql/log/dml.log lower_case_table_names=1 log_bin_trust_function_creators=1 skip-slave-start binlog-ignore-db=mysql binlog-ignore-db=information_schema binlog-ignore-db=performance_schema replicate_ignore_db=mysql replicate_ignore_db=information_schema replicate_ignore_db=performance_schema expire-logs-days=10 [mysql.server] user=mysql basedir=/data/mysql [client] socket=/data/mysql/mysql.sock (注意：1.修改后可以因为文件夹不存在，或者文件夹权限问题无法写入导致启动数据库失败) # mkdir /data/mysql/log && chown mysql.mysql /data/mysql –R 2.如果没有[client]条件的话，启动时会报错ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)) 启动master主库 # service mysql start Starting MySQL...... SUCCESS! 2．修改slave从库的配置文件（/etc/my.cnf） # vi /etc/my.cnf [mysqld] basedir =/data/mysql datadir =/data/mysql/data port =3306 server_id =3 --与主库不同即可 socket = /data/mysql/mysql.sock pid-file=/data/mysql/mysql.pid character_set_server = utf8 wait-timeout=30 max_connections = 512 default-storage-engine = Innodb log-bin =/data/mysql/log/binlog sync_binlog=1 log-error=/data/mysql/log/err.log relay-log-index =/data/mysql/relaylog/relaylogindex relay-log-info-file =/data/mysql/relaylog/relayloginfo relay-log = /data/mysql/relaylog/relaylog slow-query-log=1 long-query-time=1 slow-query-log-file=/data/mysql/log/slow.log log-error=/data/mysql/log/err.log general-log=1 general-log-file=/data/mysql/log/dml.log lower_case_table_names=1 log_bin_trust_function_creators=1 binlog-ignore-db=mysql binlog-ignore-db=information_schema binlog-ignore-db=performance_schema replicate_ignore_db=mysql replicate_ignore_db=information_schema replicate_ignore_db=performance_schema expire-logs-days=10 read-only [mysql.server] user=mysql basedir=/data/mysql [client] socket=/data/mysql/mysql.sock (注意：1.修改后可以因为文件夹不存在，或者文件夹权限问题无法写入导致启动数据库失败 # mkdir /data/mysql/relaylog/ && # mkdir /data/mysql/log/ && # chown mysql.mysql /data/mysql/ -R 2.如果没有[client]条件的话，启动时会报错ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)) 3.master数据库锁表(以便导出库时无数据操作干扰&获取二进制坐标) 授权主从复制的用户‘longshine’登录密码为‘longshine’ mysql> grant replication slave,replication client on *.* to longshine@'192.168.81.121' identified by 'longshine'; Query OK, 0 rows affected (0.22 sec) 锁表便于down数据 mysql> flush tables with read lock; --将主库锁表，保持主从的一致性 Query OK, 0 rows affected (0.05 sec) 导出数据 # mysqldump -uroot -p --all-databases >>/home/mysql/all.sql Enter password: ##-----（输入密码） 从库导入数据 # /usr/local/mysql3307/bin/mysql -uroot -p --socket=/usr/local/mysql3307/mysqld3307.sock < /home/mysql/all.sql Enter password: ##-----（输入密码） 查看主库的二进制日志状态 mysql> show master status; mysql> show master statusG *************************** 1. row *************************** File: binlog.000004 Position: 335 Binlog_Do_DB: Binlog_Ignore_DB: mysql,information_schema,performance_schema Executed_Gtid_Set: 1 row in set (0.00 sec) 从库依据主库的二进制日志状态设置与主库同步 mysql> change master to master_host = '192.168.81.14',master_port=3306,master_user='longshine',master_password='longshine',master_log_file=' binlog.000004',master_log_pos=335; 4.解锁主库 主库登录执行 mysql> unlock tables; 启动从库复制线程 mysql> start slave; Query OK, 0 rows affected (0.01 sec) 查看从库复制状态 mysql> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.81.13 Master_User: longshine Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000004 Read_Master_Log_Pos: 120 Relay_Log_File: relaylog.000007 Relay_Log_Pos: 280 Relay_Master_Log_File: binlog.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql,information_schema,performance_schema Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 120 Relay_Log_Space: 606 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: d67ab0e7-6044-11e5-8147-000c299db641 Master_Info_File: /data/mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec) （注：如果显示Slave_IO_Running: connecting，首先在主库查看主从传输用户“longshine”的权限是否出现问题，然后关闭从库线程，修改主库用户权限，重启数据库） 重建备库报错处理： 160402 9:44:24 [ERROR] Failed to open the relay log './mysql-relay-bin.000005' (relay_log_pos 1001920) 160402 9:44:24 [ERROR] Could not find target log during relay log initialization 160402 9:45:16 [ERROR] Failed to open the relay log './mysql-relay-bin.000005' (relay_log_pos 1001920) 160402 9:45:16 [ERROR] Could not find target log during relay log initialization 160402 9:45:58 [ERROR] Failed to open the relay log './mysql-relay-bin.000005' (relay_log_pos 1001920) 160402 9:45:58 [ERROR] Could not find target log during relay log initialization 分析应该是由于mysql-relay-bin.index中仍然保存着旧relay日志文件的路径，而这些路径下又找不到合适的文件，因此报错。 对于这类问题解决起来是比较简单的，重置slave的参照即可，执行命令如下： mysql> reset slave; Query OK, 0 rows affected (0.00 sec) mysql> change master to master_host = '192.168.81.121',master_port=3306,master_user='longshine',master_password='longshine',master_log_file='mysql-bin.000002',master_log_pos=9187015; Query OK, 0 rows affected (0.21 sec) mysql> start slave; Query OK, 0 rows affected (0.02 sec) 看完了这篇文章，相信你对“Linux下Mysql如何安装多实例和主从配置”有了一定的了解。 （编辑：温州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
