修改主数据库配置文件my.cnf vi /data/mysql/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[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
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust> # join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[client]
port = 3306
socket =/data/mysql/run/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
## enable autocommit
autocommit=1
general_log=off
explicit_defaults_for_timestamp=true
# system
basedir=/data/mysql
datadir=/data/mysql/data/
max_allowed_packet=134217728
max_connections=8192
max_user_connections=8000
open_files_limit=65535
pid_file=/data/mysql/run/mysqld.pid
port=3306
server_id=128
skip_name_resolve=ON
socket=/data/mysql/run/mysql.sock
tmpdir=/data/mysql/tmp
# binlog
binlog_cache_size=32768
binlog_format=row
expire_logs_days=15
log-bin=/data/mysql/log/binlog/master-bin
log-bin-index=/data/mysql/log/binlog/master-bin.index
log_slave_updates=ON
max_binlog_cache_size=2147483648
max_binlog_size=524288000
sync_binlog=100
#relay
# LOGGING #
log_error = /data/mysql/log/alert.log
log_queries_not_using_indexes = 1
slow_query_log = 1
slow_query_log_file = /data/mysql/log/slow.log
log_slave_updates=ON
log_slow_admin_statements=1
long_query_time=1
#slave#
slave_skip_errors=OFF
log_slave_updates=ON
# innodb #
innodb_log_group_home_dir=/data/mysql/log/iblog
innodb_data_home_dir=/data/mysql/log/iblog
innodb_adaptive_flushing=1
innodb_additional_mem_pool_size=20M
innodb_buffer_pool_instances=8
innodb_change_buffering=inserts
innodb_data_file_path=ibdata1:32M;ibdata2:16M:autoextend
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 4
innodb_log_file_size = 100M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_buffer_pool_size = 128M
innodb_file_format=Barracuda
innodb_file_io_threads=4
innodb_flush_neighbors=0
innodb_io_capacity=200
innodb_lock_wait_timeout=5
innodb_log_buffer_size=64M
innodb_lru_scan_depth=2048
innodb_max_dirty_pages_pct=60
innodb_old_blocks_time=1000
innodb_online_alter_log_max_size=200M
innodb_open_files=200
innodb_print_all_deadlocks=1
innodb_purge_threads=4
innodb_read_ahead_threshold=0
innodb_read_io_threads=8
innodb_rollback_on_timeout=0
innodb_sort_buffer_size=2M
innodb_spin_wait_delay=6
innodb_stats_on_metadata=0
innodb_strict_mode=1
innodb_sync_array_size=256
innodb_sync_spin_loops=30
innodb_thread_concurrency=64
innodb_use_native_aio=0
innodb_write_io_threads=8
innodb_support_xa=1
[mysqld_safe]
datadir=/data/mysql/data/ 修改从数据库my.cnf配置文件： vi /data/mysql/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[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
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'>
mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000003 | 333 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
需要用到上面标注红色的两个参数
在从服务器上登陆数据库，并配置数据库
mysql -h127.0.0.1 -uroot -P3306
mysql> set old_passwords=0;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'> mysql>stop slave;
mysql> CHANGE MASTER TO MASTER_HOST='10.140.19.189',MASTER_USER='root',MASTER_PASSWORD='root' ,MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=333;
mysql>start slave;
mysql>show slave status \G
在打印出来的状态下，查看以下两个参数的值：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果这两个参数的值都是yes，则运行正常，可以在master数据库上添加一个数据库或者添加一张表，检查slave数据库上是否存在。
如果 这两个参数有任何一个不是 Yes，则说明存在问题。可以查看slave上的数据库错误日志文件查看错误原因。 十、常见的错误原因
1、server-id 一致。
2、用户权限不够。需要的权限包括：REPLICATION SLAVE,RELOAD,CREATE USER,SUPER。
GRANT REPLICATION SLAVE,RELOAD,CREATE USER,SUPER ON *.* TO {USER}@{IP_ADDR} WITH GRANT OPTION;
3、数据库UUID一致，如果mysql安装时是通过批量复制安装的，则有可能数据库的UUID一致，进入数据库的datadir目录，修改auto.cnf。随意修改下uuid中的值，重启mysqld服务即可。
4、Master数据库端口被防火墙阻挡。
希望能帮到大家！谢谢！
QQ群⑧：
窥视卡
雷达卡
发表于 2018-10-10 11:35:32
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡