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

In the past, the MySQL environment was built using scripts used within the company, but to be honest, it shielded a lot of details, and the understanding of the installation of MySQL was relatively superficial. Today, there is a task of data migration in MySQL 5.7. in order to be familiar with the installation process, we have gone through the installation process. There is not much difference between the whole and 5.6. the installation shown here is the binary version released by Percona, which is fully compatible with the official MySQL. Of course, it also incorporated some of Percona's own things.

The first is to separate the installation path from the data path, which is particularly important. Of course, it didn't start in MySQL 5.7. although the previous installation was successful in the end, this part is a bit of a mess.

The binary package is a file like .tar.gz, which can be downloaded by yourself. Suppose we unzip it to the mysql5.7 directory, and we put it under / usr/local. This is the base camp. # mv / tmp/mysql5.7 / usr/local/mysql# mv / tmp/mysql5.7 / usr/local/mysql

Then create the corresponding data directory as needed.

# mkdir-p / home/mysql

Here we specify that the two variables basedir point to the installation directory and datadir point to the data directory

Basedir=/usr/local/mysql

Datadir=/home/mysql

Initialize the system environment, such as creating users, groups, etc.

Chattr-I / etc/shadow / etc/group / etc/gshadow / etc/passwd

/ usr/sbin/groupadd mysql

/ usr/sbin/useradd mysql-g mysql-d / home/mysql-s / sbin/nologin

Chattr + I / etc/shadow / etc/group / etc/gshadow / etc/passwd

This is the old routine.

Then copy the startup script from support-files and put it in the self-startup setting.

Cp-rf $basedir/support-files/mysql.server / etc/init.d/mysql

The following settings are to create some soft connections, and there are several common command-line tools in / usr/bin that can properly access mysql.

Chown-R mysql:mysql $basedir $datadir

Ln-f-s / usr/local/mysql/bin/mysql / usr/bin/mysql

Ln-f-s / usr/local/mysql/bin/mysqldump / usr/bin/mysqldump

Ln-f-s / usr/local/mysql/bin/mysqladmin / usr/bin/mysqladmin

Ln-f-s / usr/local/mysql/bin/mysqlshow / usr/bin/mysqlshow

Ln-f-s / usr/local/mysql/bin/mysqld / usr/bin/mysqld

Set MySQL self-startup

/ sbin/chkconfig-- add mysql

/ sbin/chkconfig-- level 2345 mysql on

In fact, look at these steps, in fact, if they are all manual files, in fact, it is also possible, this is for the sake of convenience and unified management.

Then let's do a few things, first set the parameter file, you can copy a template from support-files, improve on this, or based on the template of an existing project.

Cp $basedir/support-files/my.cnf.nor / etc/my.cnf

You still need to set the character set in this place:

Client

Default-character-set=utf8

Server side

Character-set-server = utf8

After you have finished, don't rush to service mysql start at this time, there must be the following problems.

# service mysql start

Starting MySQL (Percona Server). ERROR! The server quit without updating PID file (/ var/lib/mysql/teststd.cyou.com.pid).

If you look at the error log, you will see an obvious problem: 2016-11-09T14:15:01.952812+08:00 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

2016-11-09T14:15:01.952883+08:00 0 [ERROR] Aborting

We need to initialize the data dictionary, and it is important to note that if you use the following command, it will indicate that it is out of date.

Mysql_install_db-user=mysql-basedir=/usr/local/mysql-datadir=/home/mysql

The recommended method is:

Mysqld-initialize-user=mysql-basedir=/usr/local/mysql-datadir=/home/mysql

This operation is more successful without any log prompts.

Try again and there will be no problem.

[root@teststd bin] # service mysql start

Starting MySQL (Percona Server). SUCCESS!

The next thing to pay special attention to is the secret setting in MySQL 5.7, which sets a default password for security reasons.

If you log in directly, you will report an error.

[root@teststd bin] # mysql

Logging to file'/ home/mysql/query.log'

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

How to check the default password? it can be found in the startup log. I saw it in error.log.

[root@teststd mysql] # grep password * .log

Error.log:2016-11-09T14:28:51.344922+08:00 1 [Note] A temporary password is generated for root@localhost: aUpmj1zs8M%p

Error.log:2016-11-09T14:29:39.745255+08:00 2 [Note] Access denied for user 'root'@'localhost' (using password: NO)

Query.log:ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Follow the prompts to enter your password and you can log in successfully.

[root@teststd mysql] # mysql-u root-p

Logging to file'/ home/mysql/query.log'

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

However, you need to change your password immediately, or you will be prompted to change your password all the time.

> show databases

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

Modify the password

> set password=password ('mysql')

Query OK, 0 rows affected, 1 warning (0.01 sec)

The whole process is successfully completed, and later I want to continue to change the password. There are several ways to write it, which are more or less the same.

Update user set authentication_string=PASSWORD ('mysql') where User='root'

The next step is to build the slave library, which is incomplete without building the slave library environment.

The new version of the main library has enabled GTID

> show master status\ G

* * 1. Row *

File: mysql-bin.000002

Position: 646

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: c6d66211-a645-11e6-a2b6-782bcb472f63:1-135

1 row in set (0.00 sec)

Enable binlog, such as the following parameters:

| | log_bin | ON |

| | log_bin_basename | / home/mysql/mysql-bin |

| | log_bin_index | / home/mysql/mysql-bin.index |

| | binlog_format | ROW |

Another important setting is server-id.

[root@testdb2 ~] # mysqladmin var | grep server_id

| | server_id | 20 |

To mention briefly here, the format of server-id is relatively simple, and there can be no other characters, such as _ -, otherwise there will be an error when starting.

2016-11-09T06:48:16.918807Z 0 [ERROR] Unknown suffix'_ 'used for variable' server_id' (value '130th 58')

2016-11-09T06:48:16.918934Z 0 [ERROR] / usr/local/mysql/bin/mysqld: Error while setting value '130th 58' to' server_id'

2016-11-09T06:48:16.918981Z 0 [ERROR] Aborting

Many large companies still have some norms in this respect.

Suppose I simply set up server-id according to the end of IP.

# mysqladmin var | grep server_id

| | server_id | 58 |

Can you tell me a detail? is it server-id?

In the parameter file / etc/my.cnf is:

Server-id = 58, but if you look at the parameter settings, you can see that it is server_id, one is a horizontal line, and the other is an underscore.

[root@teststd mysql] # mysqladmin var | grep server_id

| | server_id | 58 |

We export the full library in the main library:

Main library:

[root@testdb2] # mysqldump-f-hlocalhost-uroot-- default-character-set=utf8-- single-transaction-R-- triggers-Q-- all-databases | gzip > master.dmp.gz

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass-- set-gtid-purged=OFF. To make a complete dump, pass-all-databases-triggers-routines-events.

There may be such errors in the application from the library.

# mysql

< master.dmp Logging to file '/home/mysql/query.log' ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. 很简单，我们这是一个从库，show master应该不会有GTID的信息 >

Show master status\ G

* * 1. Row *

File: mysql-bin.000005

Position: 194

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: c6d66211-a645-11e6-a2b6-782bcb472f63:1-135

1 row in set (0.00 sec)

Just do a reset operation from the library.

> reset master

Query OK, 0 rows affected (0.02 sec)

Check again, there is no GTID interference from the show master on the library.

> show master status\ G

* * 1.row * * File: mysql-bin.000001

Position: 154

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: just apply the data directly.

[root@teststd tmp] # mysql

< master.dmp Logging to file '/home/mysql/query.log' 主库的配置一个同步用户： GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl12345'; 从库使用GTID的方式自动应用 CHANGE MASTER TO MASTER_HOST='10.127.128.99', MASTER_USER='repl', MASTER_PASSWORD='repl12345', MASTER_PORT=3306, MASTER_AUTO_POSITION = 1; 然后启动从库的日志应用即可。 >

Start slave

Query OK, 0 rows affected (0.01 sec)

This is how the slave is built, and the simple verification is to use show slave status.

> show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 10.127.128.99

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000009

Read_Master_Log_Pos: 142343798

Relay_Log_File: teststd-relay-bin.000002

Relay_Log_Pos: 717

Relay_Master_Log_File: mysql-bin.000009

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

...

Exec_Master_Log_Pos: 142343798

Relay_Log_Space: 926

...

Seconds_Behind_Master: 0

...

Master_Server_Id: 20

Master_UUID: 8fc8d9ac-a62b-11e6-a3ee-a4badb1b4a00

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

...

Retrieved_Gtid_Set: 8fc8d9ac-a62b-11e6-a3ee-a4badb1b4a00:1090

Executed_Gtid_Set: 8fc8d9ac-a62b-11e6-a3ee-a4badb1b4a00:1-1090

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

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.