版本:mysql-5.6.11
LINK:mysql环境搭建
实验:主从服务器在同一台服务器,不同服务器类似。
============================
1)修改my.cnf配置文件,开启多应用
[mysqld_multi] mysqld = /usr/local/mysql/bin/mysqld_safe mysqladmin = /usr/local/mysql/bin/mysqladmin user = root #password = your_password #服务器修改密码后开启 #主服务器配置mysqld1 [mysqld1] lower_case_table_names=1 #主从服务器采用linux-windows不同系统开启 datadir = /usr/local/mysql/data pid-file = /usr/local/mysql/data/master0001.pid port = 3306 server_id = 1 socket = /tmp/mysql.sock user = mysql log_slave_updates = /usr/local/mysql/log/mysql_slave_updates.log log-error = /usr/local/mysql/log/error.log long_query_time = 1 slow_query_log_file = /usr/local/mysql/log/slowquery.log log-queries-not-using-indexes = on log-bin = /usr/local/mysql/log/binary/mysql-bin-log #备份的数据库 binlog-do-db = wp binlog-do-db = phpmyadmin #不需备份的数据库 binlog-ignore-db = mysql binlog-ignore-db = performance_schema #提交立刻写日志 sync_binlog=1 innodb-flush_logs_at_trx_commit=1 #从服务器mysqld2 [mysqld2] lower_case_table_names=1 datadir = /usr/local/mysql/data2 pid-file = /usr/local/mysql/data2/mslave0002.pid port = 3307 server_id = 2 socket = /tmp/mysql.sock2 user = mysql report-user = mslave report-host = localhost report-port = 3307 relay-log = /usr/local/mysql/log2/relay/mysql-relay relay-log-info-file = /usr/local/mysql/log2/relay/mysql-relay.info log_slave_updates = /usr/local/mysql/log2/mysql_slave_updates.log log-error = /usr/local/mysql/log2/error.log replicate-do-db = wp replicate-ignore-db = phpmyadmin replicate-ignore-db = mysql replicate-ignore-db = performance_schema
2)创建日志文件(夹)
mkdir -p /usr/local/mysql/log/binary/touch /usr/local/mysql/log/error.logtouch /usr/local/mysql/log/mysql_slave_updates.logtouch /usr/local/mysql/log/slowquery.logmkdir -p /usr/local/mysql/log2/relay/touch /usr/local/mysql/log2/error.logtouch /usr/local/mysql/log2/mysql_slave_updates.log#从(多应用的其他服务器)服务器需要copy 主服务器data/mysql 文件至自己的目录data2/mysqlcp -r /usr/local/mysql/data/mysql /usr/local/mysql/data2/mysqlchown -R mysql:mysql /usr/local/mysql#启动命令要有--defaults-file参数,否则报错mysqld_multi --defaults-file=/etc/my.cnf start 1-2 |
4)启动mysql,修改访问权限
#主服务器/usr/local/mysql/bin/mysqld_multi --defaults-file=/etc/my.cnf start 1,2/usr/local/mysql/bin/mysql -u root -S /tmp/mysql.sock |
GRANT ALL PRIVILEGES ON *.* TO "root"@"%" IDENTIFIED BY "your_password";GRANT REPLICATION SLAVE ON *.* TO "mslave"@"%" IDENTIFIED BY 'your_password2';USE mysql;UPDATE user SET Password = password('your_password') WHERE User='root';FLUSH PRIVILEGES; |
#从服务器/usr/local/mysql/bin/mysql -u root -S /tmp/mysql.sock2 |
GRANT ALL PRIVILEGES ON *.* TO "root"@"%" IDENTIFIED BY "your_password";USE mysql;UPDATE user SET Password = password('your_password') WHERE User='root';FLUSH PRIVILEGES; |
启用密码重启mysql服务(修改my.cnf)
4)配置主从同步
a.主服务器操作
FLUSH TABLES WITH READ LOCK;SHOW MASTER STATUS;+----------------------+----------+---------------+--------------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+----------------------+----------+---------------+--------------------------+-------------------+| mysql-bin-log.000147 | 105607 | wp,phpmyadmin | mysql,performance_schema | |+----------------------+----------+---------------+--------------------------+-------------------+1 row in set (0.00 sec) |
记录File 和 Position 值
备份&还原数据库
参考 mydumper多线程备份还原mysql
主服务器解锁
UNLOCK TABLES; |
b.从服务器操作
CHANGE MASTER TO MASTER_HOST='127.0.0.1',MASTER_USER='mslave',MASTER_PASSWORD='your_password2',MASTER_LOG_FILE='mysql-bin-log.000147',MASTER_LOG_POS=105607,MASTER_PORT=3306;START SLAVE;SHOW SLAVE STATUS;/* Slave_IO_Running: Yes Slave_SQL_Running: Yes 即成功,错误信息可以根据Last_IO_Errno:和Last_SQL_Error:排查。 */ |
从服务器在其它实例或服务器上配置类似,仿照[mysqld2],确保server_id值不同。