规划:部署4个mysql数据库,其中mysql1 和mysql2 互为主从,mysql3是mysql1的从,mysql4是mysql2的从,这里数据库采用的mysql-5.7.27版本,mycat采用的是Mycat-server-1.6.7.6版本,mysql主从复制。
四台服务器上都需要安装mysql数据库,一下操作都需要做一遍,前提是已经上传了mysql-5.7.27-1.el7.x86_64.rpm-bundle.tar 安装包
解压资源包,检查之前是否安装过其他版本的 mysql或者 mariadb,如果有则先删除。
#tar -xvf mysql-5.7.27-1.el7.x86_64.rpm-bundle.tar -C /usr/local/src/ #rpm -qa mysql mariadb #yum remove mysql-libs #yum -y install perl net-tools #cd /usr/local/src/ #llimage.png
开始rpm安装mysql,rpm安装会自己初始化数据库;
# rpm -vih mysql-community-common-5.7.27-1.el7.x86_64.rpm # rpm -vih mysql-community-libs-5.7.27-1.el7.x86_64.rpm # rpm -vih mysql-community-libs-compat-5.7.27-1.el7.x86_64.rpm # rpm -vih mysql-community-client-5.7.27-1.el7.x86_64.rpm # rpm -vih mysql-community-server-5.7.27-1.el7.x86_64.rpm # rpm -vih mysql-community-devel-5.7.27-1.el7.x86_64.rpm # rpm -qa | grep mysql # systemctl start mysqldimage.png
查看生成的root账号的初始密码
grep "temporary password" /var/log/mysqld.logimage.png
每个mysql服务器用初始密码登录后统一修改密码,并设置允许外部连接,然后重启服务。
# mysql -u root -p mysql> show databases; mysql> use mysql; mysql> select User,authentication_string,Host from user; mysql> alter user root@localhost identified by 'PassWod7&'; mysql> flush privileges;image.png image.png
设置允许外部连接;创建主从复制账号。
mysql> grant all privileges on *.* to "root"@"%" identified by "密码"; mysql> flush privileges; mysql> grant replication slave on *.* to 'repl'@'%' identified by '密码'; mysql> flush privileges; mysql> select User,authentication_string,Host from user;image.png
# systemctl restart mysqld
配置/etc/my.cnf,下面是mysql1上的配置文件内容,mysql2和mysql1上mysql的配置文件内容一致,只是要自增长初始值为2,和修改server-id参数即可。
# vim /etc/my.cnf #配置内容如下: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock server-id=9 #取IP地址最后一段 gtid_mode=on #开启gtid模式 enforce_gtid_consistency=on log-bin=mysql-bin # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Recommended in standard MySQL setup sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' # character set character-set-server=utf8mb4 collation-server=utf8mb4_general_ci character-set-client-handshake=FALSE # Default user user=mysql auto_increment_increment=2 #自动增长的步长 auto_increment_offset=1 #自动增长的起始数值 # slow query slow_query_log=on slow_query_log_file=/var/lib/mysql/slow-query.log # Adjust as your needed max_connections=1000 back_log=256 connect_timeout=10 key_buffer_size=16777216 innodb_buffer_pool_size=536870912 tmp_table_size=536870912 thread_cache_size=100 long_query_time=2 max_allowed_packet=200M log-slave-updates=1 #log-slave-updates用来控制本机是否把所有的操作写入到binary log,默认的情况下mysql是关闭的;会导致自己作为从机时,自己的从机复制不到数据,需要设置为开启。 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
mysql2上的my.cnf内容
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock server-id=182 gtid_mode=on enforce_gtid_consistency=on log-bin=mysql-bin # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' # character set character-set-server=utf8mb4 collation-server=utf8mb4_general_ci character-set-client-handshake=FALSE # Default user user=mysql auto_increment_increment=2 #自动增长的步长 auto_increment_offset=2 #自动增长的起始数值 # slow query slow_query_log=on slow_query_log_file=/var/lib/mysql/slow-query.log max_connections=1000 back_log=256 connect_timeout=10 key_buffer_size=16777216 innodb_buffer_pool_size=536870912 tmp_table_size=536870912 thread_cache_size=100 long_query_time=2 max_allowed_packet=200M log-slave-updates=1 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
在mysql1上登录数据库后,查看master的状态
mysql> show master status; mysql>change master to master_host='192.168.119.182', master_user='repl', master_password='密码', master_port=3306, master_auto_position=1; #启动slave mysql>start slave; mysql>show slave status\G # 如果看到两个yes ,如下所示,则表示成功 Slave_IO_Running: Yes Slave_SQL_Running: Yesimage.png
在mysql2上登录数据库后,查看master的状态
mysql> show master status; mysql>change master to master_host='192.168.119.9', master_user='repl', master_password='密码', master_port=3306, master_auto_position=1; #启动slave mysql>start slave; mysql>show slave status\G # 如果看到两个yes ,如下所示,则表示成功 Slave_IO_Running: Yes Slave_SQL_Running: Yesimage.png
在mysql3上登录数据库后,查看master的状态
mysql> show master status; mysql>change master to master_host='192.168.119.9', master_user='repl', master_password='密码', master_port=3306, master_auto_position=1; #启动slave mysql>start slave; mysql>show slave status\G # 如果看到两个yes ,如下所示,则表示成功 Slave_IO_Running: Yes Slave_SQL_Running: Yesimage.png
在mysql4上登录数据库后,查看master的状态
mysql> show master status; mysql>change master to master_host='192.168.119.182', master_user='repl', master_password='密码', master_port=3306, master_auto_position=1; #启动slave mysql>start slave; mysql>show slave status\G # 如果看到两个yes ,如下所示,则表示成功 Slave_IO_Running: Yes Slave_SQL_Running: Yes