MySQL5.7 Master-Slave Cluster Deployment Manual
1. Check the local operating system
#Be sure to check the operating system of the machine, whether it is amd (x86) or arm (aarch) architecture uname -a cat /etc/os-release
Note: MsSQL8.0 has only supported arm architecture since then. We can go to a third party to download the compiled installation package, or we can install it with docker.
2. Configure the basic environment
#Close the firewall and prohibit automatic startup at boot systemctl stop firewalld.service & amp; & amp; systemctl disable firewalld.service & amp; & amp; service iptables stop #CloseSELINUX sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/sysconfig/selinux #Create mysql users and groups, mysql users cannot log in to the system option, and do not create the user's home directory. groupadd -r mysql & amp; & amp; useradd -r -g mysql -s /sbin/nologin -M mysql #Uninstall the dependency packages that come with the system, there will be conflicts rpm -qa | grep mariadb rpm -e --nodeps mariadb-errmessage-10.3.9-9.p02.ky10.x86_64 rpm -qa | grep mysql rpm -e --nodeps delete the installed MYSQL package
3. Download the installation package
#Go to the official address to download the adm (x86) package https://downloads.mysql.com/archives/community/ #Go to the Huawei mirror site to download the arm (aarch) package https://obs.cn-north-4.myhuaweicloud.com/obs-mirror-ftp4/database/mysql-5.7.27-aarch64.tar.gz
4. Upload to server and install
Note: The information center server needs to mount its own disk. It is recommended to mount it to the /data directory.
#Unzip the installation package and extract the file contents to the /data directory tar -xvf mysql-5.7.27-aarch64.tar.gz -C /data/ #Modify the folder name to be concise cd /data mv mysql-5.7.27-aarch64/ mysql/ #Create a soft link to the configuration file ln -sf /data/mysql/my.cnf /etc/my.cnf #Create data directory mkdir mysql_data mysql_tmp mysql_logs #Authorize the directory chown -R mysql:mysql /data/mysql /data/mysql_data /data/mysql_tmp /data/mysql_logs #Override dependent packages cp -rf /data/mysql/extra/lib* /usr/lib64/ mv /usr/lib64/libstdc + + .so.6 /usr/lib64/libstdc + + .so.6.old ln -s /usr/lib64/libstdc + + .so.6.0.24 /usr/lib64/libstdc + + .so.6
1. Modify the configuration file
cd mysql cp my.cnf my.cnf_bak #Back up first vim /etc/my.cnf #Modify again
[client] # The port number for the client to connect to the MySQL server, usually 3306. port=3306 # The socket file path of the MySQL server, used for local connections. socket = /dev/shm/mysql.sock [mysqld] # The port number that the MySQL server listens to is usually 3306. port=3306 # The socket file path of the MySQL server, used for local connections. socket = /dev/shm/mysql.sock # The root directory path of MySQL, usually used to install the root directory of MySQL. basedir = /data/mysql # Directory path to store database files. datadir = /data/mysql_data # To enable the binglog log file, you can specify the directory. If not specified, it will be placed under the data directory. log_bin = mysql-bin #The file path to store the MySQL process ID. pid-file = /data/mysql_data/mysql.pid #Error log path log_error = /data/mysql_logs/mysql-error.log #Slow query sql log path slow_query_log_file = /data/mysql_logs/mysql-slow.log #Temporary data path tmpdir=/data/mysql_tmp #The user the MySQL server is running under (usually the "mysql" user) user=mysql #Used to specify the IP address bound to the MySQL server, 0.0.0.0 means bound to all available IP addresses. bind-address = 0.0.0.0 # The unique identifier of the MySQL server, used for master-slave replication, etc. server-id=1 # Initialize SQL commands when connecting to the MySQL server. init-connect = 'SET NAMES utf8mb4' # Server default character set. character-set-server = utf8mb4 #skip-name-resolve #skip-networking #The number of connections allowed to wait in the kernel back_log = 300 # The maximum number of concurrent connections allowed. max_connections = 1000 #Maximum number of connection errors max_connect_errors = 6000 # Limit the number of open files. open_files_limit = 65535 # Table cache size. table_open_cache = 128 # Maximum allowed packet size for a single query max_allowed_packet = 4M # Binary log cache size binlog_cache_size = 1M #Maximum heap table size max_heap_table_size = 8M # Temporary table size tmp_table_size = 16M # Read buffer size read_buffer_size = 2M # Random read buffer size read_rnd_buffer_size = 8M # Sorting buffer size sort_buffer_size = 8M #Connection buffer size join_buffer_size = 8M #Key buffer size key_buffer_size = 4M # Thread cache size thread_cache_size = 8 #Query cache type (1 means enabled) query_cache_type = 1 #Query cache size query_cache_size = 8M #Query cache limit query_cache_limit = 2M # Minimum word length for full-text index ft_min_word_len = 4 # Binary log file format binlog_format = mixed # Number of days for automatic cleaning of binary log files expire_logs_days = 30 # Enable slow query log (1 means enabled) slow_query_log = 1 # Define the threshold time for slow queries long_query_time = 1 #Performance mode (0 means disabled) performance_schema = 0 # Explicitly specify whether MySQL should use strict mode for checking date and time values explicit_defaults_for_timestamp # Table names are case-insensitive (1 means enabled) lower_case_table_names = 1 # Disable external locking, used to control table-level locking skip-external-locking #Default storage engine (InnoDB) default_storage_engine = InnoDB #Default storage engine (MyISAM) #default-storage-engine = MyISAM # Use separate InnoDB files for each table innodb_file_per_table = 1 #The maximum number of files that InnoDB can open innodb_open_files = 500 # InnoDB buffer pool size innodb_buffer_pool_size = 64M # Number of InnoDB write I/O threads innodb_write_io_threads = 4 # Number of InnoDB read I/O threads innodb_read_io_threads = 4 # InnoDB thread concurrency innodb_thread_concurrency = 0 #InnoDB cleaning thread number innodb_purge_threads = 1 # InnoDB log refresh behavior innodb_flush_log_at_trx_commit = 2 # InnoDB log buffer size innodb_log_buffer_size = 2M # InnoDB log file size innodb_log_file_size = 32M # Number of InnoDB log file groups innodb_log_files_in_group = 3 # InnoDB maximum dirty page percentage innodb_max_dirty_pages_pct = 90 # InnoDB lock wait timeout innodb_lock_wait_timeout = 120 #Batch insert buffer size bulk_insert_buffer_size = 8M # MyISAM sort buffer size myisam_sort_buffer_size = 8M # MyISAM maximum sort file size myisam_max_sort_file_size = 10G # MyISAM fix thread count myisam_repair_threads = 1 # Interaction timeout interactive_timeout = 28800 # Wait for timeout wait_timeout = 28800 [mysqldump] quick # mysqldump maximum allowed packet size max_allowed_packet = 100M [myisamchk] # MyISAM check tool key buffer size key_buffer_size = 8M # MyISAM check tool sort buffer size sort_buffer_size = 8M # Read cache size read_buffer = 4M # Write cache size write_buffer = 4M
2. Set automatic startup at boot
#Set to automatically start the MySQL service when the system boots. cp -rf /data/mysql/support-files/mysql.server /etc/init.d/mysqld chmod +x /etc/init.d/mysqld systemctl enable mysqld
3. Add environment variables
vim /etc/profile
# Add at the end export MYSQL_HOME=/data/mysql export PATH=$PATH:$MYSQL_HOME/bin
#Refresh environment variables source /etc/profile
4. Initialize and start MySQL
#Passwordless initialization login mysqld --initialize-insecure --user=mysql --basedir=/data/mysql --datadir=/data/mysql_data #Open mysql systemctl start mysqld #View status systemctl status mysqld
5. Change the MySQL password and allow remote connections
#Log in without password first
mysql -u root
#Use mysql library
use mysql;
#Update root password
update user set authentication_string=password("your password") where user="root";
#Give all IPs permission to connect remotely using the root user
grant all privileges on *.* to root@'%' identified by "your password";
#Refresh permission configuration
flush privileges;
#Exit mysql
exit
5. Master-slave configuration
1. Environment preparation
1. There are at least two servers, one master and one slave. The MySQL service is installed on both hosts. The /etc/my.cnf configuration file must be consistent except for the server-id.
2. The two servers need to be able to access each other’s listening port 3306.
2. Main library configuration
1. Modify the main library configuration file
# The library to be synchronized to the slave (if not written, all will be synchronized by default) #binlog-do-db=ganshuchang # No libraries for slave synchronization (multiple writes in multiple lines) binlog-ignore-db=mysql binlog-ignore-db=information_schema binlog-ignore-db=performance_schema binlog-ignore-db=performance_schema binlog-ignore-db=sys # Enable binary logging log-bin=master-bin # The unique ID of the server, usually the last segment of the IP server-id=1 log_bin_index = home/mysql/binlog/mysql-bin.index
2. Add synchronization users
#Create synchronization user create user 'repl'@'%' identified by 'repl'; #Give synchronization permissions grant replication slave on *.* to 'repl'@'%';
3. Restart the service
service mysqld restart
4. View binglog files
#Pay attention to the File and Position here, which will be used in the master-slave-slave library configuration below. show master status\G;
3. Slave library configuration
1. Modify the slave configuration file
server-id = 2 #Add the following parameters to avoid untimely updates and master-slave replication errors caused by restarting SLAVE. #Set the read-only status of the slave library to avoid writing operations on the slave library, but this command is invalid for super administrators. Mysql5.7 adds a new parameter super_read_only, which prevents super administrators from performing write operations. But most of the parameters super_read_only are turned off. read_only = 1 #Specifies that the master server's information should be stored in a special table. master_info_repository=TABLE #Specify that the information of the relay log (Relay Log) is stored in a special table relay_log_info_repository=TABLE #This is the name of the relay log relay-log = slave-relay-bin
2. Restart the service
service mysqld restart
3. Configure master-slave information
CHANGE MASTER TO MASTER_HOST = 'master_ip', -- IP address of the master server MASTER_USER = 'replication', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000001', -- the binary log file name of the master server (file in show master status\G) MASTER_LOG_POS = 123; -- The binary log position of the master server (filePosition in show master status\G)
4. Start copying
START SLAVE;
5. Check the copy status
#Make sure that both Slave_IO_Running and Slave_SQL_Running show "Yes", indicating that replication is running normally. #If Slave_IO_Running: Connecting, please check whether the slave library can remotely log in to the repl user. If not, check whether the configuration file is restricted and whether the firewall is turned off. SHOW SLAVE STATUS\G;
6. Commonly used library import statements
#Execute sql file mysql -uroot -p -D database_name > file.sql #Export the entire database mysqldump -u username -p database_name > dumpfile.sql #Export a single table mysqldump -u username -p database_name table_name > table_dump.sql