下载mysql5.7:https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
解压:tar zvxf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
转移目录:mv mysql-5.7.22-linux-glibc2.12-x86_64 /home/mysql
新建日志目录:mkdir /home/mysql/tmp
先替换/etc/my.cnf
安装①:bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=root --basedir=/home/mysql --datadir=/home/mysql/data
######执行完后生存的root@localhost: t#=sUjf1ohNf这串密码需要记住
安装②:bin/mysql_ssl_rsa_setup --datadir=/home/mysql/data
cp mysql.server /etc/init.d/mysql
--如果出现:-bash: mysql: command not found
--就执行:ln -s /home/mysql/bin/mysql /usr/bin --没有出现就不用执行
修改密码:mysql> set password=password('密码');
修改root的远程权限:mysql> grant all privileges on *.* to 'root'@'%' identified by '密码';
export PATH=/home/mysql/bin:$PATH
chkconfig --level 345 mysql on
bin/mysqld_safe --skip-grant-tables
update mysql.user set authentication_string = password('a123456') where user='root' and Host = 'localhost';
CREATE USER 'vlr'@'%' IDENTIFIED BY 'cqt@1234';
grant all privileges on *.* to 'vlr'@'%' identified by '121212f';
附my.cnf(其中一个service-id= 32128 的配置是根据IPip设置的ID)
my.cnf---------------------------------
[client]
port = 3300
socket = /var/lib/mysql/mysql.sock
[mysql]
no-auto-rehash
max_allowed_packet = 128M
#pager="less -i -n -S"
#tee=/opt/mysql/query.log
prompt = '(product)\u@\h [\d]> '
default-character-set=utf8
# The MySQL server
[mysqld]
# misc
sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
port = 3300
user = root
basedir = /home/mysql
datadir = /home/mysql/data
tmpdir = /home/mysql/tmp
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
max_allowed_packet = 32M
skip_name_resolve = 1
#timeout
interactive_timeout = 600
wait_timeout = 600
connect_timeout = 20
slave-net-timeout=30
server-id =32128
#character set
character-set-server = utf8
lower_case_table_names=1
#timeout
wait_timeout=100
interactive_timeout=100
open_files_limit = 65535
back_log = 1024
max_connections=1100
max_user_connections=1000
max_connect_errors = 1000000
external-locking = FALSE
skip-name-resolve
event_scheduler=1
log_timestamps = system
#logs
log-output="FILE"
slow_query_log = 1
slow-query-log-file = /home/mysql/logs/slow.log
log-error = /home/mysql/logs/error.log
log_error_verbosity = 2
pid-file = mysql.pid
long_query_time=1
slow_query_log = on
log-slow-slave-statements = 1
#log_slow_verbosity = "full"
#log-queries-not-using-indexes = 1
#log_throttle_queries_not_using_indexes = 10
explicit_defaults_for_timestamp = 1
default-time-zone = '+8:00'
log-slow-admin-statements = 1
#binlog
binlog_format = row
log-bin = /home/mysql/logs/mysql-bin
binlog_cache_size = 1M
max_binlog_cache_size = 2G
max_binlog_size = 1G
expire-logs-days = 7
#procedure
log_bin_trust_function_creators = 1
#gtid
gtid_mode = on
enforce_gtid_consistency = 1
#relay log
#skip_slave_start = 1
#slave-skip-errors=all
slave-skip-errors=1062,1050,1051,1146,1145,1061,1032,1304,1396,1537,1539
max_relay_log_size = 1G
relay_log_purge = 1
relay_log_recovery = 1
#slave_parallel_workers = 2
master_verify_checksum = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE
slave_sql_verify_checksum = 1
slave_allow_batching = 1
log_slave_updates
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
#buffers & cache
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache_instances = 64
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 768
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 512K
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
tmp_table_size = 96M
max_heap_table_size = 96M
#myisam
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
lock_wait_timeout = 3600
#innodb
innodb_data_home_dir = /home/mysql/data
innodb_log_group_home_dir = /home/mysql/logs
innodb_data_file_path = ibdata1:1G:autoextend
innodb_buffer_pool_size = 45G
#innodb_buffer_pool_instances = 8
innodb_spin_wait_delay = 30
sync_binlog=1
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 64M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_lock_wait_timeout = 10
innodb_file_per_table = 1
innodb_rollback_on_timeout= 1
innodb_print_all_deadlocks = 1
innodb_status_file = 1
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 300
#innodb_max_io_capacity = 20000
innodb_flush_neighbors = 1
innodb_adaptive_flushing = 1
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
transaction_isolation = READ-COMMITTED
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_sync_spin_loops = 100
#innodb_support_xa = 0
innodb_thread_concurrency = 0
innodb_thread_sleep_delay = 500
#innodb_file_io_threads = 4
innodb_concurrency_tickets = 1000
#innodb_file_format = Barracuda
innodb_purge_threads=4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_purge_batch_size = 32
innodb_old_blocks_pct=75
innodb_change_buffering=all
innodb_online_alter_log_max_size = 4G
internal_tmp_disk_storage_engine = InnoDB
innodb_stats_on_metadata = 0
innodb_max_undo_log_size=4G
innodb_status_output = 0
innodb_status_output_locks = 0
#performance_schema
performance_schema = 1
performance_schema_instrument = '%=on'
#innodb monitor
innodb_monitor_enable="module_innodb"
innodb_monitor_enable="module_server"
innodb_monitor_enable="module_dml"
innodb_monitor_enable="module_ddl"
innodb_monitor_enable="module_trx"
innodb_monitor_enable="module_os"
innodb_monitor_enable="module_purge"
innodb_monitor_enable="module_log"
innodb_monitor_enable="module_lock"
innodb_monitor_enable="module_buffer"
innodb_monitor_enable="module_index"
innodb_monitor_enable="module_ibuf_system"
innodb_monitor_enable="module_buffer_page"
innodb_monitor_enable="module_adaptive_hash"
#semi replication
#plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=3000 # 3000 second
rpl_semi_sync_slave_enabled=1
rpl_semi_sync_master_wait_for_slave_count=1
#group relplication
binlog_group_commit_sync_delay=10
binlog_group_commit_sync_no_delay_count=20
#parallel write
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=16
# FOR Percona 5.7
#thread_handling = pool-of-threads
#thread_pool_oversubscribe = 16
#extra_port = 33306
#extra_max_connections = 2
[mysqldump]
quick
max_allowed_packet = 128M
#myisam_max_sort_file_size = 10G
[mysqld_safe]
malloc-lib= /usr/lib64/libjemalloc.so
[mysqld_multi]
mysqld = /home/mysql/bin/mysqld_safe
mysqladmin = /home/mysql/bin//mysqladmin
log = /home/data/mysqld_multi.log
-------------------------------------------
先使用以下语句查看主的log信息
show master status;
//双主即两个都执行change对方地址
change master to
master_host='10.10.1.12',
master_user='root',
master_password='xxxxxxx',
master_port=3300,
master_log_file='mysql-bin.000003',
master_log_pos=153225199;
9)启动slave:start slave;
10)查看slave状态:show slave status\G
注:其中Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES,才表明状态正常。
3、测试:
在主节点创建数据库,插入数据,在从节点查看是否同步出现。(略)
至此,主从同步搭建完毕!
##两台都执行
查看主同步文件及pos
show master status;