怎么安装Mysql双机热备
这篇文章主要介绍“怎么安装MySQL双机热备”,在日常操作中,相信很多人在怎么安装Mysql双机热备问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”怎么安装Mysql双机热备”的疑惑有所帮助!接下来,请跟着小编一起来学习吧!
在双鸭山等地区,都构建了全面的区域性战略布局,加强发展的系统性、市场前瞻性、产品创新能力,以专注、极致的服务理念,为客户提供成都网站设计、做网站 网站设计制作定制制作,公司网站建设,企业网站建设,成都品牌网站建设,成都营销网站建设,外贸营销网站建设,双鸭山网站建设费用合理。
一、安装mysql
#tar -xf mysql-5.7.18-1.el6.x86_64.rpm-bundle.tar #yum localinstall *.rpm
1.1修改mysql配置
# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/data/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid explicit_defaults_for_timestamp=true tmpdir=/tmp [client] default-character-set=utf8mb4 [mysqld] character_set_server=utf8mb4
1.2权限修改
[root@172 ~]# chown -R mysql:mysql /data [root@172 ~]# chmod 777 -R /data/ [root@172 ~]# chmod -R 777 /tmp
1.3启动mysql服务
[root@172 ~]# service mysqld restart Stopping mysqld: [FAILED] Initializing MySQL database: [ OK ] Installing validate password plugin: [ OK ] Starting mysqld: [ OK ]
1.4查看temp密码
more /var/log/mysqld.log |grep temporary
1.5修改root密码
db1
ALTER USER 'root'@'localhost' IDENTIFIED BY '*****'; flush privileges; exit;
db2
ALTER USER 'root'@'localhost' IDENTIFIED BY '*****'; flush privileges; exit;
二、配置主从同步
master1 | 172.28.8.187 |
---|---|
master2 | 172.28.8.188 |
2.1 配置master1给master2登录的密码
Master1
create user 'repl' identified by '*****'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.28.8.188' IDENTIFIED BY '*****'; FLUSH PRIVILEGES; mysql> create database mydb default charset utf8;
在172.28.8.188测试repuser是否能登录172.28.8.187上的数据库
mysql -urepl -p -h272.28.8.187
2.1.1 Master1配置my.cnf
# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/data/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid explicit_defaults_for_timestamp=true tmpdir=/tmp character_set_server=utf8mb4 server-id=177 log-bin=/var/log/mysql/mysql-bin.log read-only=0 binlog-ignore-db=mysql binlog-ignore-db=information_schema expire_logs_days= 365 auto-increment-increment = 2 auto-increment-offset = 1 [client] default-character-set=utf8mb4
2.2 Master2配置my.cnf
#除server-id外,其他与master1保持一致
2.2.1 Master2给Master1创建账号密码并授权
create user 'repl' identified by '*****'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.28.8.187' IDENTIFIED BY '*****'; FLUSH PRIVILEGES;
2.3 查看Master同步状态
master1
mysql> show master status; +------------------+----------+--------------+--------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+--------------------------+-------------------+ | mysql-bin.000001 | 154 | mydb | mysql,information_schema | | +------------------+----------+--------------+--------------------------+-------------------+ 1 row in set (0.00 sec)
master2
mysql> show master status; +------------------+----------+--------------+--------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+--------------------------+-------------------+ | mysql-bin.000001 | 154 | mydb | mysql,information_schema | | +------------------+----------+--------------+--------------------------+-------------------+ 1 row in set (0.00 sec)
设置master1从master2同步
mysql>CHANGE MASTER TO MASTER_HOST='172.28.8.188',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='b4l:GGtG3s0*',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=860; mysql> SHOW SLAVE STATUS\G mysql> START SLAVE; mysql> SHOW SLAVE STATUS\G
设置master2从master1同步
mysql>CHANGE MASTER TO MASTER_HOST='172.28.8.187',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='2S1*8pr+BzqH^8T`',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=1497; mysql> SHOW SLAVE STATUS\G mysql> START SLAVE; mysql> SHOW SLAVE STATUS\G
如出现以下两项,则说明配置成功!
Slave_IO_Running: Yes Slave_SQL_Running: Yes
3.双主同步测试
进入master1 mysql 数据库
mysql> create database crm; Query OK, 1 row affected (0.00 sec) mysql> use crm; Database changed mysql> create table employee(id int auto_increment,name varchar(10),primary key(id)); Query OK, 0 rows affected (0.00 sec) mysql> insert into employee(name) values('a'); Query OK, 1 row affected (0.00 sec) mysql> insert into employee(name) values('b'); Query OK, 1 row affected (0.00 sec) mysql> insert into employee(name) values('c'); Query OK, 1 row affected (0.06 sec) mysql> select * from employee; +----+------+ | id | name | +----+------+ | 1 | a | | 3 | b | | 5 | c | +----+------+ 3 rows in set (0.00 sec)
进入master2,查看是否有crm这个数据库和employee表。
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | crm | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.00 sec) mysql> use crm; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +---------------+ | Tables_in_crm | +---------------+ | employee | +---------------+ 1 row in set (0.00 sec) mysql> select * from employee; +----+------+ | id | name | +----+------+ | 1 | a | | 3 | b | | 5 | c | +----+------+ 3 rows in set (0.00 sec) mysql> insert into employee(name) values('d'); Query OK, 1 row affected (0.00 sec) mysql> select * from employee; +----+------+ | id | name | +----+------+ | 1 | a | | 3 | b | | 5 | c | | 7 | d | +----+------+ 4 rows in set (0.00 sec)
在master1的中查看是否有刚刚在master2中插入的数据。
mysql> select * from employee; +----+------+ | id | name | +----+------+ | 1 | a | | 3 | b | | 5 | c | | 7 | d | +----+------+ 4 rows in set (0.00 sec)
到此,关于“怎么安装Mysql双机热备”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注创新互联网站,小编会继续努力为大家带来更多实用的文章!
本文题目:怎么安装Mysql双机热备
网页链接:http://myzitong.com/article/pjjohg.html