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

Operating system version

CENTOS7.6

MYSQL5.7.22

What? Why my operating system does not have an iso installation image, alas, can not get a local yum source, then get a network. It's just that you can't use it by default, so do it yourself.

Cd / etc/yum.repos.d/

Mkdir bak

Cp * .repo bak/

# wget http://mirrors.163.com/.help/CentOS7-Base-163.repo

-bash: wget: command not found

WTF? It was.

The original CentOS-Base.repo has been deleted, so there is no way to install the wget command, so use curl-O to get

# curl-O http://mirrors.163.com/.help/CentOS7-Base-163.repo

Vi CentOS7-Base-163.repo

Replace something, otherwise it may not be able to use

:% s#$releasever#7#g

Yum list

OKroomYUM source is done, let's start building MYSQL

1. MYSQL source code installation

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

Installation environment:

Operating system CENTOS7.6

NODE1 hostname tsdb1 IP address 192.168.159.4

NODE2 hostname tsdb2 IP address 192.168.159.5

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.7.22 source code package

Https://downloads.mysql.com/archives/get/file/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

4. Decompress tar-zxvf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

Cd / opt/

Tar-zxvf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz-C / home/

Cd / home/

Mv mysql-5.7.22-linux-glibc2.12-x86_64 mysql5.7.22

5. Installation

Mkdir-p / home/mysql5.7.22/data/

Cd / home/

Ln-s mysql5.7.22 mysql

Cd mysql

Chown-R mysql:mysql. /

/ bin/mysqld-- initialize-- user=mysql-- basedir=/home/mysql5.7.22/-- datadir=/home/mysql5.7.22/data/-- lc_messages_dir=/home/mysql5.7.22/share-- lc_messages=en_US

.

/ bin/mysqld-- initialize-- user=mysql-- basedir=/home/mysql5.7.22/-- datadir=/home/mysql5.7.22/data/-- lc_messages_dir=/home/mysql5.7.22/share-- lc_messages=en_US

2019-05-25T13:01:50.716538Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use-explicit_defaults_for_timestamp server option (see documentation for more details).

2019-05-25T13:01:51.897953Z 0 [Warning] InnoDB: New log files created, LSN=45790

2019-05-25T13:01:52.039050Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2019-05-25T13:01:52.116959Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 43c9f84c-7eed-11e9-a241-fefcfe4975bb.

2019-05-25T13:01:52.118956Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2019-05-25T13:01:52.119626Z 1 [Note] A temporary password is generated for root@localhost: kar) * .OtV4F)

The installation speed is fast, and it will be done with one swipe.

Kar) * .OtV4F)

This is an automatically generated temporary password.

(if you see a direct installation that reported this error)

Bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

Solution: yum install-y libaio / / install and then initialize the OK)

# change all files in the mysql/ directory except the data/ directory back to those owned by root users

Chown-R root.

# mysql users only need to be the owners of all files in the mysql-5.7.22/data/ directory

Chown-R mysql data

5. Copy startup file

Cd / home/mysql/

Cp. / support-files/mysql.server / etc/init.d/mysqld

Chmod 755 / etc/init.d/mysqld

Cp. / bin/my_print_defaults / usr/bin/

6. Modify startup script

Vi / etc/init.d/mysqld

# modified items:

Basedir= / home/mysql5.7.22/

Datadir= / home/mysql5.7.22/data/

Port=3306

# add the environment variable and edit / etc/profile, so you can use the mysql command anywhere

Vi / etc/profile

# add the mysql path, add the following, and press ESC-- >: wq to save

Export PATH=$PATH:/home/mysql5.7.22/bin

Source / etc/profile

7. Modify mysql configuration item

Vi / etc/my.cnf

[mysqld]

Basedir = / home/mysql5.7.22

Datadir = / home/mysql5.7.22/data

8. Start mysql

Service mysqld start

When you encounter a situation that cannot be started, the first is to modify the configuration file to the simple way above, and the second is to delete the files in the data directory and reinitialize them.

Mysql-uroot-pkar) * .OtV4F)

Encounter a situation where an error message cannot be started, for example:

2019-12-31T07:49:42.258557Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.

2019-12-31T07:49:42.259636Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2019-12-31T07:49:42.259803Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key

2019-12-31T07:49:42.260385Z 0 [Note] Server hostname (bind-address):'*'; port: 3306

2019-12-31T07:49:42.260446Z 0 [Note] IPv6 is available.

2019-12-31T07:49:42.260462Z 0 [Note] -':: 'resolves to'::

2019-12-31T07:49:42.260487Z 0 [Note] Server socket created on IP:':'.

2019-12-31T07:49:42.261927Z 0 [Note] InnoDB: Loading buffer pool (s) from / data/mysql/mysql/ib_buffer_pool

2019-12-31T07:49:42.262277Z 0 [Note] InnoDB: Buffer pool (s) load completed at 191231 15:49:42

2019-12-31T07:49:42.264556Z 0 [Warning] Failed to open optimizer cost constant tables

2019-12-31T07:49:42.264839Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

2019-12-31T07:49:42.264937Z 0 [ERROR] Aborting

(encountered this error problem, it may be due to the incorrect setting of the installation directory, I tried to reinstall myself

A test library, and then did not press the directory settings on the notes, set up a new different directory as follows

[mysqld]

Basedir=/data/mysql/

Datadir=/data/mysql/data/

[mysqld_safe]

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

Then reset it when you reinstall, as follows:

/ bin/mysqld-- initialize-- user=mysql-- basedir=/data/mysql/-- datadir=/data/mysql/data/-- lc_messages_dir=/data/mysql-5.7.22-linux-glibc2.12-x86_64/share/-- lc_messages=en_US

Then the user of the installation directory should also remember to change it to mysql)

The first thing to do after logging in is to change the password first, otherwise you will make a mistake in typing any command and honestly change the password.

Enter mysql to modify the initial password and modify the user rights of the remote connection.

Alter user 'root'@'localhost' identified by' TaB) * .Wtf4F)'

Flush privileges; # Refresh permissions

Mysql-uroot-pTaB) * .Wtf4F)

Use mysql

Update user set host ='% 'where user =' root'

Flush privileges

The boot of the CENTOS7.6 version will not start automatically, and the permission needs to be modified.

Chmod + x / etc/rc.d/rc.local

Master-slave building

Main library configuration

Vi / etc/my.cnf

[mysqld]

Basedir=/home/mysql5.7.22/

Datadir=/home/mysql5.7.22/data/

Max_connections=3000

Default-storage-engine=INNODB

Character-set-server=utf8

Slow_query_log_file = / home/mysql5.7.22/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

# 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

Restart

Service mysqld restart

Main library configuration synchronization user

Grant replication slave on *. * to 'Master-slave'@'192.168.159.5' identified by' TaB) * .Wtf4F)'

Flush privileges

Mysql > show master status

+-+

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

+-+

| | mysql-bin.000002 | 601 | Master-slave | mysql | |

+-+

1 row in set (0.00 sec)

Slave library configuration

Vi / etc/my.cnf

[mysqld]

Basedir = / home/mysql5.7.22

Datadir = / home/mysql5.7.22/data

Max_connections=3000

Default-storage-engine=INNODB

Character-set-server=utf8

Slow_query_log_file = / home/mysql5.7.22/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

# slave

Server-id=2

Log-bin= mysql-bin

Relay-log= mysql-relay-bin

Read- >

Log-slave-updates=1

Replicate-do-db=Master-slave

Expire_logs_days=7

Restart

Service mysqld restart

Query the ID number and log file number in the main database

Show master status

Log in to the slave library again

Slave library

Mysql > CHANGE MASTER TO MASTER_HOST='192.168.159.4',MASTER_USER='Master-slave',MASTER_PASSWORD='TaB) * .Wtf4F)', MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=601,MASTER_CONNECT_RETRY=10

Start slave

View

Show slave status\ G

There are still some doubts here. For example, if I add the following information to the configuration file, I will not be able to start the database.

[mysqld_safe]

Log-error=/home/mysql5.7.22/log/tsdb1_error.log

Pid-file=/home/mysql5.7.22/data/tsdb1.pid

You will encounter an error when starting.

Service mysqld start

Starting MySQL. ERROR! The server quit without updating PID file (/ home/mysql5.7.22/data//tsdb1.pid).

It can't be solved by looking up the information all the time.

The above are all based on a variety of online materials plus my own problems, summed up and revised, only as notes written to myself, easy to check the next time I build, but the function that the setting is only for myself is missing.

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

[mysqld]

Default-time_zone ='+ 8VR 00'

Use the time zone of Beijing time

Error 1045 password error

Select * from mysql.user where user='root'

Use mysql

Update user set authentication_string=password ('123456') where user='root' and host='localhost'

Update user set authentication_string=password ('123456') where user='root' and host='%'

Flush privileges

Error 1086 password expires

Use mysql

Update user set password_expired= "N" where user= "root"

Flush privileges

Master-slave switching

In mysql master-slave relationship, if master goes down, you should promote slave as master.

After the original master library is restored, the old master library should be changed into a slave library.

Only in this way can we ensure the high availability of business and the integrity of data at the application level as much as possible.

# # switching from mysql Master / Slave to Slave Master:

Change from library to master library

To switch from the current slave library to the master library:

Show processlist;# ensures Slave has read all relay log

STOP SLAVE IO_THREAD

Show slave status\ G; check whether the IO and SQL threads are normal. If it is NO, the synchronization is inconsistent.

Stop slave

Reset master

Reset slave all; (see version number)

Grant replication slave on *. * repl@'IP' identified by 'password'

Show master status;# remember the log_file and log_pos information listed here

# # Master database to slave database

Will be switched from the current master library to the slave library

Reset master; (new slave, old master)

Reset slave

Change master to master_host= "IP"

Master_port=3306

Master_user= "repl"

Master_password= "password"

Master_log_file= "...", # log_file listed in the previous step

Master_log_pos= "..."; # log_pos listed in the previous part

Start slave

Show slave status\ G

At present, it is only a basic building, and there are still many optimized parameters that have not been verified and tested, which will be added later.

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.