#tar -xf mysql-5.7.18-1.el6.x86_64.rpm-bundle.tar #yum localinstall *.rpm1.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 = 2Mdatadir=/data/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidexplicit_defaults_for_timestamp=truetmpdir=/tmp[client]default-character-set=utf8mb4[mysqld]character_set_server=utf8mb41.2权限修改
[root@172 ~]# chown -R mysql:mysql /data[root@172 ~]# chmod 777 -R /data/[root@172 ~]# chmod -R 777 /tmp1.3启动mysql服务
[root@172 ~]# service mysqld restartStopping mysqld: [FAILED]Initializing MySQL database: [ OK ]Installing validate password plugin: [ OK ]Starting mysqld: [ OK ]1.4查看temp密码
more /var/log/mysqld.log |grep temporary1.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 |
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.1872.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 = 2Mdatadir=/data/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidexplicit_defaults_for_timestamp=truetmpdir=/tmpcharacter_set_server=utf8mb4server-id=177log-bin=/var/log/mysql/mysql-bin.logread-only=0binlog-ignore-db=mysqlbinlog-ignore-db=information_schemaexpire_logs_days= 365auto-increment-increment = 2auto-increment-offset = 1[client]default-character-set=utf8mb42.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\Gmysql> 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\Gmysql> START SLAVE;mysql> SHOW SLAVE STATUS\G
Slave_IO_Running: Yes Slave_SQL_Running: Yes3.双主同步测试
进入master1 mysql 数据库
mysql> create database crm;Query OK, 1 row affected (0.00 sec)mysql> use crm;Database changedmysql> 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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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中的ERROR 1135 (HY000)报错问题
下一篇：MySQL安装支持的平台有哪些
mysql
