Shulou(Shulou.com)05/31 Report--

This article mainly introduces the relevant knowledge of "how to deploy Mysql with one master and multiple slaves". The editor shows you the operation process through an actual case. The method of operation is simple, fast and practical. I hope this article "how to deploy Mysql with one master and multiple slaves" can help you solve the problem.

Download the tar.gz package

Select redhat version

Install mysql1 user useradd mysql12. Mysql1 on the 1.linux system. Upload the tar.gz package to the server and extract tar-zxvf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz3. Mv the files under the mysql-5.7.31-el7-x86_64 directory to the 4.mkdir-p / home/mysql1/data file under / home/mysql1 to store the mysql1 data directory mkdir-p / home/mysql1/data5. Copy the / etc/my.cfg file to cp / etc/my.cnf / home/mysql1/6 in the mysql home directory. Weight chmod-R 755 / home/mysq1lchown mysql:mysql-R / home/mysql1/datachmod 777 / home/mysql1/data7 under / home/mysql1. Modify mysql configuration file

Vi / home/mysql1/my.cnf

[mysqld] user = port = 3307basedir = / home/mysql1datadir = / home/mysql1/datasocket = / home/mysql1/mysql.sockpid_file = / home/mysql1/mysql.pidlog_error = / home/mysql1/data/error.log#binlog log file log_bin = / home/mysql1/data/mysql-binrelay_log = / home/mysql1/data/relay-binslow_query_log_file = / home/mysql1/data/slow.log#binlog expiration cleanup time expire_logs_days = 15log-slave -updates = 1log_bin_trust_function_creators = 1lower_case_table_names = 1max_connections = 3000max_connect_errors = 1000000 # each instance's id is set to different For example, host a, host 3307 b is set to 23307 and host c is set to 33307 The latter 1 master 2 slave will be used by server-id = 3307 autocommit = "pool_size" to update according to the actual situation innodb_buffer_pool_size = 40Ginnodb_buffer_pool_instances = 8innodb_write_io_threads = 16innodb_read_io_threads = 16#mysql replication there are three main One way: replication based on SQL statements (statement-based replication SBR) Row-based replication (row-based replication, RBR), mixed mode replication (mixed-based replication, MBR). Correspondingly, there are three formats of binlog: STATEMENT,ROW MIXEDbinlog_format = ROWgtid_mode = onenforce_gtid_consistency = 1innodb_flush_log_at_trx_commit = 1 # add 2 innodb_thread_concurrency = 20innodb_print_all_deadlocks = 1innodb_flush_method = O_DIRECTinnodb_io_capacity = 8000innodb_io_capacity_max = 15000enforce_gtid_consistency = 1binlog_rows_query_log_events = 1character_set_server = utf8mb4default-storage-engine = INNODBtransaction_isolation = READ-COMMITTEDmax_allowed_packet = 67108864event_scheduler = 1slow_query_log = onexplicit_defaults_for_timestamp = 1 master_info_repository = TABLErelay_log_info_repository = TABLErelay_log_recovery = 1relay_log_purge = 0 slave_rows_search_algorithms = 'INDEX_SCAN HASH_SCAN'slave_parallel_type = LOGICAL_CLOCKslave_parallel_workers = 16slave_preserve_commit_order = 1slave_transaction_retries = 64sync_relay_log = 0sync_relay_log_info = 0sync_master_info = 0sync_binlog = 1collation_server = utf8mb4_binskip_name_resolve = 1 plugin-load = "rpl_semi_sync_master=semisync_master.so Rpl_semi_sync_slave=semisync_slave.so "rpl-semi-sync-master-enabled = 1rpl-semi-sync-slave-enabled = 18. Data file initialization

1. Initialization

. / mysqld-defaults-file=/home/mysql1/my.cnf-initialize-basedir=/home/mysql1-datadir=/home/mysql1/data-user=mysql1

two。 Start

. / mysqld_safe-defaults-file=/home/mysql1/my.cnf-user=mysql1 &

Check that the mysql process has been set up and listen on interface 3307

3. Log in to this machine. The initial password of root can be found in errorlog.

Cat error.log | grep "temporary password"

4. Log in to the specified sock file and specify the port and enter the temporary password on this machine

. / mysql-uroot-P3307-S / home/mysql1/mysql.sock-p

5. Change the root password after logging in

Set password=password ('XXXXXXXX'); change password flush privileges

6. Grant authority

Grant all privileges on *. * to 'root'@'%' identified by' XXXXXXX' with grant option;flush privileges

7. Out of Service

. / mysqladmin shutdown-uroot-p*-S / home/mysql1/mysql.sock successfully connected to mysql using mysql client datagrip

Install the slave library by doing the same as above, modify only the information related to the configuration file, initialize, and the client connects

Execute on the master database

1. Create a repl user and grant synchronization permissions

CREATE USER 'repl' IDENTIFIED BY' slavepass';GRANT REPLICATION SLAVE ON *. * TO 'repl'@'%' identified by' slavepass';flush privileges

two。 View master status

SHOW MASTER STATUS\ G

Execute on slave

1. Configure the master node and binlog offset position of the slave slave node, etc.

CHANGE MASTER TO MASTER_HOST='master address', MASTER_USER='repl',MASTER_PASSWORD='slavepass',MASTER_LOG_FILE='mysql-bin.000003', # is the log_fileMASTER_LOG_POS=1597 of master, and POSMASTER_PORT=3307 of # master

two。 Start the slave synchronization process and view the slave status

Start slave; # starts the slave synchronization process show slave status\ G

test

Create a testdb in mysql1, and use datagrip to view the new libraries and data synchronized from mysql1 in mysql2, but datagrip needs to refresh the client.

This is the end of the content about "how to deploy Mysql with one master and more slaves". Thank you for your reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.