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

Mysql database installation:

Server IP address Planning:

Master server IP 172.20.2.46 Slave server IP 172.20.2.47

Second, install mysql server

1. Unzip the downloaded installation to the / usr/local directory

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

two。 Enter the / usr/local directory

Cd / usr/local/

3. Create a soft link for the mysql installation directory

Ln-s mysql-5.7.22-linux-glibc2.12-x86_64 mysql

4. Add mysql user groups and mysql users for centos (the-s / bin/false parameter specifies that the mysql user has only ownership and no login rights)

Groupadd mysql

Useradd-r-g mysql-s / bin/false mysql

5. Enter the directory where the mysql software is installed, with the following command

Cd / usr/local/mysql

6. Change the current directory owner to the newly created mysql user with the following command:

Chown-R mysql:mysql. /

7. Install mysql with the following command:

. / bin/mysqld-user=mysql-basedir=/usr/local/mysql-datadir=/usr/local/mysql/data-initialize

8. If the installation is successful as shown in the following figure, the system generates a password by default.

9. Start the mysql service with the following command:

. / support-files/mysql.server start

If the following error occurs, the path in the mysql configuration file / etc/my.cnf is incorrect. The modification is as follows. Both datadir and socket are modified to the installation directory of mysql, and the [client] section is added to connect to the mysql database on the command line.

#

[mysqld]

Port=3306

Datadir=/usr/local/mysql/data

Socket=/usr/local/mysql/mysql.sock

User=mysql

Max_connections=151

Symbolic-links=0

Set to ignore case

Lower_case_table_names = 1

Specify the code

Character-set-server=utf8

Collation-server=utf8_general_ci

Enable ip binding

Bind-address = 0.0.0.0

[mysqld_safe]

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

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

# specify the socket communication file path when the client connects to mysql

[client]

Socket=/usr/local/mysql/mysql.sock

Default-character-set=utf8

#

9. Restart the mysql service, as shown in the following figure, the startup is successful!

. / support-files/mysql.server start

10. Put the mysql process into the system process with the following command:

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

11. Restart the mysql service with the following command:

. / support-files/mysql.server start

twelve。 Configure mysql environment variables

Vi / etc/profile

Export PATH=$PATH:/usr/local/mysql/bin

Save exit, and then compile:

Source / etc/profile

13. Log in to the mysql database with a random password with the following command:

Mysql-u root-p

Enter a random password to log in successfully, as shown below:

14. Go to the mysql operation line and set the new password for the root user (here set to youpassword):

1. Modify the user's password

Mysql > alter user 'root'@'localhost' identified by' youpassword'

Or

Mysql > set password=password ("youpassword")

2. Refresh permissions

Mysql > flush privileges

#

Master-slave configuration process:

Master server IP 172.20.2.46 Slave server IP 172.20.2.47

1) Master server configuration:

[root@MySQL-M ~] # vim / etc/my.cnf # add the following.

[mysqld]

# give the server a unique ID.

Server_id=46

# Open binary log.

Log-bin=mysql-bin # mysql-bin is the log name.

# specify the binary log format.

Binlog-format=mixed

Note:

#

Mixed #

Row # binary records the disk.

Stateement # binary record execution statements, such as update

# # #

Which one is better?

Update age=age+1 where id=3; / / statements are long and disk changes are small, so it is appropriate to use row

The update salary=salary+100; / / statement is short, but it affects tens of thousands of lines and the disk changes greatly, so it is suitable to use stateement.

Mixed, mixed, is decided by the system according to the statement.

#

2) configure from the server:

[root@MySQL-S ~] # vim / etc/my.cnf

[mysqld]

# give the server a unique ID.

Server-id=47

# Open binary log. # # on the one hand, if the data is lost after recovery, it can also be used as one master and multiple slaves. It is recommended to open it.

Log-bin=mysql-bin

# specify the binary log format.

Binlog-format=mixed

Enable the relay-log relay log function.

Relay-log=mysql-relay

#

Two servers start the service.

[root@MySQL-M mysql] #. / support-files/mysql.server restart

[root@MySQL-S mysql] #. / support-files/mysql.server restart

#

Master server operation:

[root@MySQL-M] # mysql-u root-p

Enter password:

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

Your MySQL connection id is 20220

Server version: 5.7.22-log MySQL Community Server (GPL)

Mysql >

Check to see if master has the status condition to act as master.

Mysql > show master status

+-+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | Position # byte offset.

+-+

| | mysql-bin.000001 | 1559 | |

+-+

1 row in set (0.00 sec)

Primary server authorization:

Mysql > grant replication client,replication slave on. To repl@'172.20.2.%' identified by 'repl'

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

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

#

From the server:

Mysql > change master to

-> master_host='172.20.2.46'

-> master_user='repl'

-> master_password='repl'

-> master_log_file='mysql-bin.000001'

-> master_log_pos=1559

Query OK, 0 rows affected, 2 warnings (0.05 sec)

Mysql > show slave status

+- -+ -+ -+-

Mysql > show slave status\ G

1. Row

Slave_IO_State: Waiting for master to send event

Master_Host: 172.20.2.46

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 1559

Relay_Log_File: mysql-relay.000003

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp:

Start the slave server function

Mysql > start slave

#

Test:

Primary server:

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | |

| | mysql |

| | performance_schema |

| | sys |

| | test8 |

| | zabbix |

+-+

7 rows in set (0.01 sec)

Mysql > create database wagnbo

->

Query OK, 1 row affected (0.01sec)

From the server:

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | |

| | mysql |

| | performance_schema |

| | sys |

| | test |

| | test8 |

| | wagnbo |

| | zabbix |

+-+

9 rows 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.