Shulou(Shulou.com)06/01 Report--

Foreword:

The previous 5.6.38 master-slave article is a joke in the test system, and the following article is about the MYSQL master-slave built on the company's production base.

Before going on the system, I struggled for a long time, which kind of database to use (PG? HBASE? MONGODB? )? Finally, MYSQL was chosen because the company understands many people, and they are familiar with it, and the initial amount of data of the new project is small, MYSQL can meet the needs, the most important thing is that this new project is of a pathfinding nature, if it is really taken seriously, then the data will be migrated out of the good (based on the development that the data structure is simple and single, the case of single storage data, there is no special data type, etc.).

So I finally chose MYSQL, a member of the open source database. (originally, I would have considered ORACLE, but considering that the leaders support open source, let's use open source.)

Before also entangled whether to use mysql5.7.22, as a result to download the installation, found that the cmake version of redhat6.5 is too low, does not support mysql5.7.22 compilation, suddenly did not find the installation package of redhat7, just use the final stable version of mysql5.6.40.

Operating system, network card, firewall, IP address configuration and so on will not be written here. 1. MYSQL source code installation

(both nodes are installed, the steps are the same)

Installation environment:

Operating system REDHAT6.5

NODE1 hostname master IP address 192.168.159.148

NODE2 hostname slave IP address 192.168.159.149

1. Installation dependency

Yum-y install make gcc-c++ cmake bison-devel ncurses-devel perl

2. Create users and groups

Groupadd mysql

Useradd mysql-g mysql-M-s / sbin/nologin

3. Download 5.6.40 source code package

Https://downloads.mysql.com/archives/get/file/mysql-5.6.40.tar.gz

4. Decompress tar-zxvf mysql-5.6.40.tar.gz

Cd / opt/

Tar-zxvf mysql-5.6.40.tar.gz

Cd mysql-5.6.40

5. Compile and install

Cmake-DCMAKE_INSTALL_PREFIX=/usr/local/mysql-DMYSQL_DATADIR=/usr/local/mysql/data-DSYSCONFDIR=/etc-DWITH_MYISAM_STORAGE_ENGINE=1-DWITH_INNOBASE_STORAGE_ENGINE=1-DWITH_MEMORY_STORAGE_ENGINE=1-DWITH_READLINE=1-DMYSQL_UNIX_ADDR=/tmp/mysqld.sock-DMYSQL_TCP_PORT=3306-DENABLED_LOCAL_INFILE=1-DWITH_PARTITION_STORAGE_ENGINE=1-DEXTRA_CHARSETS=all-DDEFAULT_CHARSET=utf8-DDEFAULT_COLLATION=utf8_general_ci

Description:

Set up the installation directory

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql

Database storage directory

-DMYSQL_DATADIR=/usr/local/mysql/data

System configuration directory

-DSYSCONFDIR=/etc

Install the myisam storage engine

-DWITH_MYISAM_STORAGE_ENGINE=1

Install the innodb storage engine

-DWITH_INNOBASE_STORAGE_ENGINE=1

Install the memory storage engine

-DWITH_MEMORY_STORAGE_ENGINE=1

Shortcut key function

-DWITH_READLINE=1

Unix socket file path

-DMYSQL_UNIX_ADDR=/tmp/mysqld.sock

MySQL listening port

-DMYSQL_TCP_PORT=3306

Allow data to be imported locally

-DENABLED_LOCAL_INFILE=1

Install database partition

-DWITH_PARTITION_STORAGE_ENGINE=1

Install all extended character sets

-DEXTRA_CHARSETS=all

Use utf8 characters

-DDEFAULT_CHARSET=utf8

Check character

-DDEFAULT_COLLATION=utf8_general_ci

Support for SSL

-DWITH_SSL=yes

Make & & make install

6. Directory authorization

Chown mysql.mysql / usr/local/mysql

Mkdir / usr/local/mysql/log

Chown mysql.mysql / usr/local/mysql/log

7. Database initialization

Cd / usr/local/mysql

. / scripts/mysql_install_db-user=mysql-datadir=/usr/local/mysql/data

8. Configure environment variables

Vi / root/.bash_profile is added on the last line

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin:/usr/local/mysql/lib

2. Master-slave construction of MYSQL5.6.40

1. Master-side configuration deployment a, add the following configuration under the [mysqld] node in the my.cnf configuration file on the main server

Vi / etc/my.cnf

[mysqld]

Server-id=101 default-storage-engine=InnoDB lower_case_table_names=1 log-bin=/usr/local/mysql/log/mysql-bin.log log-bin-index=/usr/local/mysql/log/mysql-bin.index expire_logs_days=30 datadir=/usr/local/mysql/data socket=/tmp/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [mysqld_safe] log-error=/usr/local/mysql/data/mysqld.log pid-file=/usr/local/mysql/mysqld.pid

Description:

Log-bin: give all the file base names of the binary log

Log-bin-index: gives the file name of the binary log file, usually starting with 000001, incremented sequentially. Full name: master-bin.000001

Server-id: the mysql server is unique ID and must be unique among all servers replicated from the master.

Start the database

Traditional startup mode

/ usr/local/mysql/bin/mysqld_safe-user=mysql &

Make it into service startup

Cp / usr/local/mysql/support-files/mysql.server / etc/init.d/mysql

Check to see if the startup is successful

Netstat-tnl | grep 3306

Ps-ef | grep mysql

Related command

Service mysql start

Stop the mysql service

Service mysql stop

Restart the mysql service

Service mysql restart

Add to boot startup item

Chkconfig-add mysql

B. Create users and grant permissions: it is wrong to log in to the database.

ERROR 2002 (HY000): Can't connect to local MySQL server through socket'/ tmp/mysqld.sock' (2)

Ln-s / tmp/mysql.sock / tmp/mysqld.sock

Landing

Mysql-uroot

Create user repl_user

GRANT REPLICATION SLAVE ON *. * TO 'repl'@'%' IDENTIFIED BY PASSWORD' *'

You will encounter an error when setting the password:

ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number

Solution: use select password ('password you want to enter'); query the string corresponding to your password

Select password ('123456')

Found out is * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9

GRANT REPLICATION SLAVE ON *. * TO 'repl_user'@'%' IDENTIFIED BY PASSWORD' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'

2. Configure deployment an on Slave end. Configuration parameters: [mysqld]

Vi / etc/my.cnf

[mysqld] server-id=102 default-storage-engine=InnoDB lower_case_table_names=1 log-bin=/usr/local/mysql/log/mysql-bin.log log-bin-index=/usr/local/mysql/log/mysql-bin.index expire_logs_days=30 datadir=/usr/local/mysql/data socket=/tmp/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0

[mysqld_safe] log-error=/usr/local/mysql/data/mysqld.log pid-file=/usr/local/mysql/mysqld.pid

3. Establish master-slave synchronization

(this method is also used to rebuild the standby library.)

To establish master-slave synchronization, you can export data from the master library or from an existing slave library, and then import it into a new slave library, and change master to establishes synchronization.

(if it is a new master and slave, you can directly install the slave library according to the method of installing the master library. They are all brand new, so you do not need to do the following export and import operations.)

3.1. Export data

Export data on the main library:

Mysqldump-u***-p***-S / data/mysql6001/mysql.sock-- default-character-set=utf8-Q-- single-transaction-- master-data-A > / tmp/all_database.sql

(or) export data from the library:

Mysqldump-u***-p***-S / data/mysql6001/mysql.sock-- default-character-set=utf8-Q-- single-transaction-- dump-slave-A > / tmp/all_database.sql

NOTES:

-- master-data and-- dump-slave export backups that contain master_log_file and master_log_pos information.

Example:

Mysqldump-uroot-- events-- all-databases > / opt/mysql.dump

3.2. Import data from the library

Mysql-upright bands *-paired bands *-- default-character-set=utf8

< all_database.sql 例子： mysql -uroot -p*** < /opt/mysql.dump 3.3、从库与主机建立同步 以下为建立主从同步最基本的6个项：change master to master_host='xxx.xxx.xxx.xxx', # 主库IP master_port=6001, # 主库mysqld的端口 master_user='repl', # 主库中创建的有REPLICATION SLAVE权限的用户 master_password='xxxxxxxx', # 该用户的密码 master_log_file='mysql-bin.000xxx', # 已在导入时指定了 master_log_pos=xxxxxx; #已在导入时指定了 start slave; 例子： master_log_file和master_log_pos通过在主库上使用命令获得： show master status \G; 在从库上执行： change master to master_host='192.168.159.148', master_port=3306, master_user='repl_user', master_password='123456', master_log_file='mysql-bin.000002', master_log_pos=415; start slave; 验证主从是否搭建成功在从库执行 show slave status \G 在主库创建一个表 use test create table aa (name char(10)); insert into aa values('Tom'); 在从库查询 use test select * from aa; 查到刚刚插入的数据就O了 修改root密码 cd /usr/local/ mysql /bin ./mysqladmin -u root password mysql>

Use mysql;mysql > desc user;mysql > GRANT ALL PRIVILEGES ON *. * TO root@ "%" IDENTIFIED BY "root"; / / authorize remote connection mysql > update user set Password = password ('123456') where User='root'; / / set root user password mysql > select Host,User,Password from user where User='root';mysql > flush privileges;mysql > exit

2. MYSQL5.6.40 master building

The previous installation configuration is the same, only need to configure / etc/my.cnf

1. Main library A configuration (192.168.159.148)

Vi / etc/my.cnf

[client] port = 3306 socket = / tmp/ mysql.sock [mysqld] basedir = / usr/local/mysqlport = 3306 socket = / tmp/mysql.sockdatadir = / usr/local/mysql/datapid-file = / usr/local/mysql/data/mysql.pidlog-error = / usr/local/mysql/data/mysql.errserver-id = 1 auto_increment_offset = 1 auto_increment_increment = 2 # odd IDlog-bin = mysql-bin # enable binary function The MASTER master server must open this binlog-format=ROW#binlog-row-p_w_picpath=minimal # this parameter does not know why it reported an error log-slave-updates=true gtid-mode=onenforce-gtid-consistency=true master-info-repository=TABLErelay-log-info-repository=TABLE sync-master-info=1 slave-parallel-workers=0 sync_binlog=0 binlog-checksum=CRC32master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 # expire_logs_days=5 max_binlog_size=1024M # binlog single file maximum replicate-ignore-db = mysql # ignore master-slave database replicate-ignore-db = information_schemareplicate-ignore-db = performance_schemareplicate-ignore-db = testreplicate-ignore-db = zabbixmax_connections = 3000 max_connect_errors = 30 skip-character-set-client-handshake # ignore other character sets that the application wants to set init-connect='SET NAMES utf8' # SQLcharacter-set-server=utf8 # server default character set wait_timeout=1800 # request maximum connection time interactive_timeout=1800 # and the previous parameter modified at the same time to take effect sql_mode=NO_ENGINE_SUBSTITUTION STRICT_TRANS_TABLES # sql mode max_allowed_packet = 10Mbulk_insert_buffer_size = 8Mquery_cache_type = 1 query_cache_size = 128Mquery_cache_limit = 4Mkey_buffer_size = 256Mread_buffer_size = 16Kskip-name-resolveslow_query_log=1 long_query_time = 6 slow_query_log_file=slow-query.loginnodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 16M [MySQL] no-auto-rehash [myisamchk] key_buffer_size = 20Msort_buffer_size = 20Mread_buffer = 2Mwrite_buffer = 2m [mysqlhotcopy] interactive- timeout [mysqldump] quickmax_allowed_packet = 16m [mysqld _ safe]

2. Configuration of main library B (192.168.159.149)

Vi / etc/my.cnf

[client] port = 3306 socket = / tmp/ mysql.sock [mysqld] basedir = / usr/local/mysqlport = 3306 socket = / tmp/mysql.sockdatadir = / usr/local/mysql/datapid-file = / usr/local/mysql/data/mysql.pidlog-error = / usr/local/mysql/data/mysql.errserver-id = 2 auto_increment_offset = 2 auto_increment_increment = 2 # even IDlog-bin = mysql-bin # enable binary function The MASTER master server must open this binlog-format=ROW#binlog-row-p_w_picpath=minimal # this parameter does not know why it reported an error log-slave-updates=true gtid-mode=onenforce-gtid-consistency=true master-info-repository=TABLErelay-log-info-repository=TABLE sync-master-info=1 slave-parallel-workers=0 sync_binlog=0 binlog-checksum=CRC32master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 # expire_logs_days=5 max_binlog_size=1024M # binlog single file maximum replicate-ignore-db = mysql # ignore master-slave database replicate-ignore-db = information_schemareplicate-ignore-db = performance_schemareplicate-ignore-db = testreplicate-ignore-db = zabbixmax_connections = 3000 max_connect_errors = 30 skip-character-set-client-handshake # ignore other character sets that the application wants to set init-connect='SET NAMES utf8' # SQLcharacter-set-server=utf8 # server default character set wait_timeout=1800 # request maximum connection time interactive_timeout=1800 # and the previous parameter modified at the same time to take effect sql_mode=NO_ENGINE_SUBSTITUTION STRICT_TRANS_TABLES # sql mode max_allowed_packet = 10Mbulk_insert_buffer_size = 8Mquery_cache_type = 1 query_cache_size = 128Mquery_cache_limit = 4Mkey_buffer_size = 256Mread_buffer_size = 16Kskip-name-resolveslow_query_log=1 long_query_time = 6 slow_query_log_file=slow-query.loginnodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 16M [MySQL] no-auto-rehash [myisamchk] key_buffer_size = 20Msort_buffer_size = 20Mread_buffer = 2Mwrite_buffer = 2m [mysqlhotcopy] interactive- timeout [mysqldump] quickmax_allowed_packet = 16m [mysqld _ safe]

3. Main library A (192.168.159.148) creates synchronous users

Mysql > grant replication slave on *. * to 'repl'@'192.168.159.149' identified by' 123456 unknown MySQL > flush privileges

4. Create synchronous users in main library B (192.168.159.149)

Mysql > grant replication slave on *. * to 'repl'@'192.168.159.148' identified by' 123456 unknown MySQL > flush privileges

5. Main library A (192.168.159.148) configure synchronization information

Mysql > show master status

+ -- +

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+ -- +

| | mysql-bin.000003 | 2552 | 7b0fba4f-5cd4-11e8-bada-000c29ba59e8:1-20 |

A1788b59-5d6a-11e8-bead-000c295d547a:3-9 |

+ -- +

1 row in set (0.00 sec)

Mysql > change master to master_host='192.168.159.149',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=1727

Mysql > start slave

Mysql > show slave status\ G

Notice that these two states are YES, that is, normal.

Slave_IO_Running: YesSlave_SQL_Running: Yes

6. Main library A (192.168.159.149) configure synchronization information

Mysql > show master status

+ -- +

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+ -- +

| | mysql-bin.000004 | 1727 | 7b0fba4f-5cd4-11e8-bada-000c29ba59e8:8-10:16-20 |

A1788b59-5d6a-11e8-bead-000c295d547a:1-9 |

+ -- +

1 row in set (0.05sec)

Mysql > change master to master_host='192.168.159.148',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=2552

Mysql > start slave

Mysql > show slave status\ G

Notice that these two states are YES, which is normal Slave_IO_Running: YesSlave_SQL_Running: Yes

7. Detect master synchronization

You can create a database in library An and see if it is synchronized in library B, then create a database in library B and see if it is synchronized in library A.

Note:

In the case of abnormal mysql downtime, if sync_binlog=1 or innodb_flush_log_at_trx_commit=1 is not set, it is very likely that the binlog or relaylog file will be corrupted, resulting in inconsistency of the master.

-- to be continued

8. MYSQL starts the error report

(1)

Error reporting phenomenon:

[root@node1 mysql] # service mysql start

Starting MySQL.. ERROR! The server quit without updating PID file (/ var/lib/mysql/node1.pid).

View the error log:

[root@node1 mysql] # tail / var/log/mysqld.log

190512 19:59:10 InnoDB: Starting an apply batch of log records to the database...

InnoDB: Progress in percents: 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99

InnoDB: Apply batch completed

190512 19:59:10 InnoDB: Waiting for the background threads to start

190512 19:59:11 InnoDB: 5.5.40 started; log sequence number 1595675

190512 19:59:11 [Note] Server hostname (bind-address): '0.0.0.0; port: 3306

190512 19:59:11 [Note]-'0.0.0.0' resolves to' 0.0.0.0'

190512 19:59:11 [Note] Server socket created on IP: '0.0.0.09.

190512 19:59:11 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

190512 19:59:11 mysqld_safe mysqld from pid file / var/lib/mysql/node1.pid ended

Solution:

Check the configuration file and find that the datadir directory in the configuration file is default and needs to be modified to your own / usr/local/mysql/data/.

[root@node1 mysql] # cat / etc/my.cnf

[mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

User=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

Symbolic-links=0

[mysqld_safe]

Log-error=/var/log/mysqld.log

Pid-file=/var/run/mysqld/mysqld.pid

[root@node1 mysql] # vi / etc/my.cnf

Datadir=/usr/local/mysql/data/

Restart MYSQL successfully after modification is completed

[root@node1 mysql] # service mysql start

Starting MySQL.. SUCCESS!

(2)

Error reporting phenomenon:

[root@node1 mysql] # / usr/local/mysql/bin/mysql-uroot

ERROR 2002 (HY000): Can't connect to local MySQL server through socket'/ tmp/mysql.sock' (2)

Check the configuration file and find that the socket file is in / var/lib/mysql/mysql.sock, not in / tmp/mysql.sock

[root@node1 mysql] # cat / etc/my.cnf

[mysqld]

Datadir=/usr/local/mysql/data/

Socket=/var/lib/mysql/mysql.sock

User=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

Symbolic-links=0

[mysqld_safe]

Log-error=/var/log/mysqld.log

Pid-file=/var/run/mysqld/mysqld.pid

Solution:

Set up soft links

Ln-s / var/lib/mysql/mysql.sock / tmp/mysql.sock

Alternatively, modify the configuration file

Pay attention to a problem, in the production environment, we should pay attention to the time zone.

Vi / etc/my.cnf

[mysqld]

Default-time_zone ='+ 8VR 00'

Use the time zone of Beijing time

The production environment should also pay attention to the setting of the number of connections (the operating system should also be set)

Max_connections=3000

An example like the following will do.

[mysqld]

Basedir=/XXX/ fill in by yourself.

Datadir=/XXX/ fill in by yourself.

Max_connections=3000

Default-storage-engine=INNODB

Character-set-server=utf8

Slow_query_log_file = / XXX/ fill in / log/mysql-slow.log

Slow_query_log=1

Long_query_time = 5

Log-queries-not-using-indexes = 1

Bind-address=0.0.0.0

Socket=/tmp/mysql.sock

User=mysql

Default-time_zone ='+ 8VR 00'

# master

Server-id=1

Log-bin=mysql-bin

Log-bin-index=mysql-bin.index

Log-slave-updates=1

Binlog-do-db=Master-slave

Binlog-ignore-db=mysql

Binlog_format=mixed

Expire_logs_days=7

Let me add:

MYSQL also has a key parameter related to performance, a performance problem encountered in the production environment

Innodb_flush_log_at_trx_commit

This parameter controls how the redo log is written to disk. We know that InnoDB uses a "Write Ahead Log" strategy to avoid data loss, that is, relying on redo logs to ensure that data can be recovered after loss. Therefore, the persistence of InnoDB redo logs is very important.

Valid values for this parameter are 0, 1, 2:

0: when a transaction commits, the redo log buffer is not written to disk, but relies on the main thread of InnoDB to perform a flush to disk once per second. So if MySQL goes down, it's possible to lose some transactions.

1: when a transaction commits, the redo log buffer is written to disk and immediately refreshed (fsync ()). Note that because of the "delayed write" feature of the operating system, the brush is only written to the operating system's buffer, so performing a synchronization operation ensures that it must be persisted to the hard disk.

2: when a transaction commits, the redo log buffer is written to disk, but it is not immediately flushed, so it is only written to the operating system buffer. At this time, if the operating system goes down without even synchronization, some data may be lost.

As you can see, only 1 can really guarantee the persistence of the transaction, but because the refresh operation fsync () is blocked and does not return until it is finished, we know that the speed of writing to disk is very slow, so the performance of MySQL will degrade significantly. If you don't care about transaction loss, 0 and 2 can achieve higher performance.

Sync_binlog

This parameter controls the process by which binary logs are written to disk.

The valid values for this parameter are 0, 1, N:

0: default value. After the transaction is committed, the binary log is written to disk from the buffer, but no refresh operation is performed (fsync ()). At this time, only the operating system buffer is written, and some of the binary log is lost if the operating system goes down.

1: after the transaction is committed, the binaries are written to disk and refreshed immediately, which is equivalent to writing to disk synchronously without going through the operating system's cache.

N: every time the operating system buffer is written, a refresh operation is performed.

Binary log files involve data recovery and want to achieve maximum consistency between master and slave, so you should set this parameter to 1, but it will also cause some performance loss.

Typically, both parameters are set to 1 to ensure data security, but if performance is more important in some cases, consider setting it to another value for maximum performance.

Set to 1, which is the safest, but has a large performance loss.

Set to 0, it is the most efficient, but the least safe.

Set to 2, as long as the operating system does not crash, data will not be lost

Generally, there is a master-slave architecture that can be set to (the master architecture can also be used, or there is no high requirement for data security). The advantage of this setting is to improve the performance of IO, and there is also a certain performance improvement in the face of large concurrency.

Innodb_flush_log_at_trx_commit=2

Sync_binlog=0

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.