mysqlMaster：1.1.1.22,mysqlMaster.t4x.org 2.6.32-573.22.1.el6.x86_64,CentOS release 6.7
mysqlSlave：1.1.1.101,mysqlSlave.t4x.org 3.10.0-327.13.1.el7.x86_64,CentOS Linux release 7.2.1511
基础环境：(多实例配置)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
[root@mysqlMaster ~]# yum install vim gcc gcc-c++ wget ncurses-devel net-tools -y [root@mysqlMaster ~]# yum install openssl openssl-devel pcre-devel cmake libaio* library* ncurses-* -y [root@mysqlMaster tools]# wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.49.tar.gz [root@mysqlMaster tools]# tar -zxf mysql-5.5.49.tar.gz [root@mysqlMaster tools]# cd mysql-5.5.49 [root@mysqlMaster mysql-5.5.49]# cmake . -DCMAKE_INSTALL_PREFIX=/byrd/service/mysql-5.5.49 -DMYSQL_UNIX_ADDR=/byrd/service/mysql-5.5.49/tmp/mysql.sock -DMYSQL_DATADIR=/byrd/service/mysql-5.5.49/data -DWITH_DEBUG=0 -DEXTRA_CHARSETS=all -DWITH_READLINE=1 -DWITH_ZLIB=system -DWITH_SSL=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_USER=mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 [root@mysqlMaster mysql-5.5.49]# make && make install [root@mysqlMaster mysql-5.5.49]# mkdir /data/{3306,3307}/data -p [root@mysqlMaster mysql-5.5.49]# chown -R mysql:mysql /data/ [root@mysqlMaster mysql-5.5.49]# groupadd -r mysql -g 27 #设置mysql用户组(-r系统组也就是0-500，centos7为0-1000) [root@mysqlMaster mysql-5.5.49]# useradd -r mysql -u 27 -g mysql -d /data/ -s /sbin/nologin #色和在系统用户，默认不会设置home目录 [root@mysqlMaster mysql-5.5.49]# ln -s /byrd/service/mysql-5.5.49 /usr/local/mysql5 [root@mysqlMaster mysql-5.5.49]# /usr/local/mysql5/scripts/mysql_install_db --basedir=/usr/local/mysql5/ --datadir=/data/3306/data --user=mysql [root@mysqlMaster mysql-5.5.49]# /usr/local/mysql5/scripts/mysql_install_db --basedir=/usr/local/mysql5/ --datadir=/data/3307/data --user=mysql [root@mysqlSlave /]# /usr/local/mysql5/scripts/mysql_install_db --basedir=/usr/local/mysql5/ --datadir=/data/3308/data --user=mysql [root@mysqlSlave /]# /usr/local/mysql5/scripts/mysql_install_db --basedir=/usr/local/mysql5/ --datadir=/data/3309/data --user=mysql [root@mysqlMaster 3306]# cat /data/3306/my.cnf | grep -v ^ [client] port = 3306 socket = /data/3306/mysql.sock [mysql] no-auto-rehash [mysqld] user = mysql port = 3306 socket = /data/3306/mysql.sock basedir = /usr/local/mysql5 datadir = /data/3306/data [root@mysqlMaster 3306]# /usr/local/mysql5/bin/mysqld_safe --defaults-file=/data/3306/my.cnf & [root@mysqlMaster 3306]# /usr/local/mysql5/bin/mysqld_safe --defaults-file=/data/3307/my.cnf & [root@mysqlMaster 3306]# netstat -tunlp|grep mysql tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 13416/mysqld tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 13265/mysqld [root@mysqlMaster 3306]# /usr/local/mysql5/bin/mysql -uroot -p -S /data/3306/mysql.sock #首次空密码 [root@mysqlMaster 3306]# /usr/local/mysql5/bin/mysql -uroot -p'admin123' -S /data/3306/mysql.sock [root@mysqlMaster 3306]# /usr/local/mysql5/bin/mysql -uroot -p'admin456' -S /data/3307/mysql.sock [root@mysqlMaster /]# /usr/local/mysql5/bin/mysqld_safe --defaults-file=/data/3306/my.cnf & [root@mysqlMaster /]# /usr/local/mysql5/bin/mysqld_safe --defaults-file=/data/3307/my.cnf & mysql> update mysql.user set password=PASSWORD('admin123') where user='root'; mysql> flush privileges; |
配置环境：(主从同步配置)
3306主库:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
[root@mysqlMaster /]# /usr/local/mysql5/bin/mysql -uroot -p'admin123' -S /data/3306/mysql.sock mysql> select user,host from mysql.user; +------+--------------------+ | user | host | +------+--------------------+ | root | 127.0.0.1 | | root | ::1 | | | localhost | | root | localhost | | | mysqlslave.t4x.org | | root | mysqlslave.t4x.org | +------+--------------------+ mysql> delete from mysql.user; Query OK, 6 rows affected (0.00 sec) mysql> select * from mysql.user\G mysql> grant all privileges on *.* to system@'localhost' identified by 'admin123' with grant option; mysql> flush privileges; mysql> select * from mysql.user\G *************************** 1. row *************************** Host: localhost User: system Password: *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C mysql> create database da3306t21 default character set utf8 collate utf8_general_ci; mysql> drop database test; mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | da3306t21 | | mysql | | performance_schema | +--------------------+ mysql> grant replication slave on *.* to byrd@'1.1.1.101' identified by 'passwd123'; #授权从库ip mysql> select user,host from mysql.user; +--------+-----------+ | user | host | +--------+-----------+ | byrd | 1.1.1.101 | | system | localhost | +--------+-----------+ mysql> flush tables with read lock; #锁表 mysql> show variables like '%timeout%'; #查询超时时间，默认28800 mysql> show variables like '%timeout%'; +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | interactive_timeout | 28800 | | wait_timeout | 28800 | +----------------------------+----------+ mysql> show master status; #获取bin_log位置点 +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 188 | | | +------------------+----------+--------------+------------------+ [root@mysqlMaster /]# /usr/local/mysql5/bin/mysqldump -usystem -p'admin123' -S /data/3306/mysql.sock --events -B -A | gzip > /tmp/all.sql.gz mysql> show master status; #查询位置是否偏移（-F 除外） +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 188 | | | +------------------+----------+--------------+------------------+ [root@mysqlMaster /]# scp -P22 /tmp/all.sql.gz root@1.1.1.101:/tmp root@1.1.1.101's password: all.sql.gz 100% 148KB 148.2KB/s 00:00 [root@mysqlMaster /]# egrep "server-id|log" /data/3306/my.cnf log-bin = /data/3306/data/mysql-bin server-id = 1 |
3308从库配置：SourceByrd's Weblog-https://note.t4x.org/database/multi-mysqld-master-slave-synchronization/
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
[root@mysqlSlave /]# egrep "server-id|log" /data/3308/my.cnf #需要重新启动mysql #log-bin = /data/3308/data/mysql-bin #备份服务器、下面有其他从库【级联同步】，需要开bin_log server-id = 999 [root@mysqlSlave /]# ll /tmp/ -rw-r--r--. 1 root root 151725 Apr 22 01:34 all.sql.gz [root@mysqlSlave /]# gzip -d /tmp/all.sql.gz [root@mysqlSlave /]# ll /tmp/ -rw-r--r--. 1 root root 552100 Apr 22 01:34 all.sql [root@mysqlSlave /]# /usr/local/mysql5/bin/mysql -usystem -p'admin123' -S /data/3308/mysql.sock ERROR 1045 (28000): Access denied for user 'system'@'localhost' (using password: YES) [root@mysqlSlave /]# /usr/local/mysql5/bin/mysql -uroot -p'' -S /data/3308/mysql.sock mysql> select user,host from mysql.user; +--------+-----------+ | user | host | +--------+-----------+ | byrd | 1.1.1.101 | | system | localhost | +--------+-----------+ mysql> flush privileges; [root@mysqlSlave /]# /usr/local/mysql5/bin/mysql -usystem -p'admin123' -S /data/3308/mysql.sock mysql> CHANGE MASTER TO -> MASTER_HOST='1.1.1.22', -> MASTER_PORT=3306, -> MASTER_USER='byrd', -> MASTER_PASSWORD='passwd123', -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=188; Query OK, 0 rows affected (0.25 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 1.1.1.22 Master_User: byrd Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 188 Relay_Log_File: mysqlSlave-relay-bin.000002 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 |
参考文档：SourceByrd's Weblog-https://note.t4x.org/database/multi-mysqld-master-slave-synchronization/
多实例配置：https://note.t4x.org/database/mysql-instance-configuration/
主从同步：https://note.t4x.org/database/configuration-mysql-master-slave/SourceByrd's Weblog-https://note.t4x.org/database/multi-mysqld-master-slave-synchronization/
SourceByrd's Weblog-https://note.t4x.org/database/multi-mysqld-master-slave-synchronization/
申明：除非注明Byrd's Blog内容均为原创,未经许可禁止转载!详情请阅读版权申明!
