Mysql主备安装

 生产环境mysql主备安装操作示例

(IP根据实际情况修改)

master:192.168.1.109    service-id:109

slave:   192.168.1.106    service-id:106

同步账号:sync   同步密码:sync

主：master:192.168.1.109 步骤

配置同步账号

#创建目录
mkdir /usr/local/mysql/binlog
#重新授权
chown -R mysql:mysql /usr/local/mysql


mysql -uroot -p


update user set host='%' where user='sync';

mysql>CREATE USER 'sync'@'%' IDENTIFIED BY 'sync';
mysql>ALTER USER 'sync'@'%' IDENTIFIED BY 'Ab1234568';
#授权
mysql>use mysql
mysql>grant all privileges on *.* to 'sync'@'%'; 
mysql>flush privileges;
mysql>exit


vi /use/local/mysql/my.cnf
  
  server-id=109
  log-bin=mysql-bin
 
  
  
service mysqld restart

mysql -uroot -h 127.0.0.1 -p
 
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      155 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

mysql> show processlist;

+----+-----------------+-------------------+--------------------+-------------+-------+---------------------------------------------------------------+------------------+
| Id | User            | Host              | db                 | Command     | Time  | State                                                         | Info             |
+----+-----------------+-------------------+--------------------+-------------+-------+---------------------------------------------------------------+------------------+
|  4 | event_scheduler | localhost         | NULL               | Daemon      | 52286 | Waiting on empty queue                                        | NULL             |
| 90 | sync            | 192.168.1.103:52240 | information_schema | Sleep       |    11 |                                                               | NULL             |
| 91 | root            | localhost:51382   | NULL               | Query       |     0 | starting                                                      | show processlist |
| 92 | sync            | 192.168.1.106:49122 | NULL               | Binlog Dump |    84 | Master has sent all binlog to slave; waiting for more updates | NULL             |
+----+-----------------+-------------------+--------------------+-------------+-------+---------------------------------------------------------------+------------------+
4 rows in set (0.00 sec)


Command为Binlog Dump为正常。

备：192.168.1.106 步骤

#新建目录
mkdir /usr/local/mysql/relaylog
#重新授权
chown -R mysql:mysql /usr/local/mysql

vi /usr/local/mysql/my.cnf



#[mysqld]下面
  server-id=106
 
  
service mysqld restart

mysql -uroot -h 127.0.0.1 -p
 
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.109',MASTER_USER='sync',MASTER_PASSWORD='Ab1234568',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=155;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status;


mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)
 
mysql> change master to 
    -> master_host='192.168.1.109',
    -> master_port=3306,
    -> master_user='sync',
    -> master_password='Ab1234568',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=155;
    
    
mysql> start slave;
    
mysql> show slave status \G;


如果出现：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
以上两项都为Yes，那说明没问题了。

数据库配置 文件更新：

master 配置my.cnf文件：

[mysqld]
server_id=109
log-bin=/usr/local/mysql/binlog/mysql-bin
#设置最大100MB
max_binlog_size=104857600
#设置了只保留7天BINLOG（单位：天）
expire_logs_days = 7
#binlog日志只记录指定库的更新
binlog-do-db=liri_ext

#数据表不区分大小写
#lower_case_table_names=1

#设置3306端口
port=3306

# 自定义设置mysql的安装目录，即解压mysql压缩包的目录
basedir=/usr/local/mysql

# 自定义设置mysql数据库的数据存放目录
datadir=/usr/local/mysql/data
socket=/var/lib/mysql/mysql.sock
#错误日志
log-error=/usr/local/mysql/logs/error.log


# 允许最大连接数
max_connections=20000
# 允许连接失败的次数，这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10

# 服务端使用的字符集默认为UTF8
character-set-server=utf8

# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
#sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

#分组最大值
group_concat_max_len = 204800

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8

[client]
# 设置mysql客户端连接服务端时默认使用的端口和默认字符集
port=3306
default-character-set=utf8
socket=/var/lib/mysql/mysql.sock
slave配置my.cnf文件：



[mysqld]

server-id=106
#(数据表不区分大小写)
#lower_case_table_names='1'

#设置最大100MB
max_binlog_size=104857600
#设置了只保留7天BINLOG（单位：天）
expire_logs_days = 7

#设置要进行或不要进行主从复制的数据库名
replicate-do-db=liri_ext
replicate-ignore-db=mysql,information_schema
#中继日志信息

relay_log=/usr/local/mysql/relaylog/relay-bin
relay_log_index=/usr/local/mysql/relaylog/relay-bin.index
relay_log_info_file=/usr/local/mysql/relaylog/relay-log.info


#设置3306端口
port=3306

# 自定义设置mysql的安装目录，即解压mysql压缩包的目录
basedir=/usr/local/mysql

# 自定义设置mysql数据库的数据存放目录
datadir=/usr/local/mysql/data
socket=/var/lib/mysql/mysql.sock
#错误日志
log-error=/usr/local/mysql/logs/error.log


# 允许最大连接数
max_connections=20000
# 允许连接失败的次数，这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10

# 服务端使用的字符集默认为UTF8
character-set-server=utf8

# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
#sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
#sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

#分组最大值
group_concat_max_len = 204800

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8

[client]
# 设置mysql客户端连接服务端时默认使用的端口和默认字符集
port=3306
default-character-set=utf8
socket=/var/lib/mysql/mysql.sock