Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people are at a loss about how to expand mysql by migrating the directory of a library. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Description of the problem: there is a set of master and master replicated mysql database with a data file directory exceeding 97%, which is seriously insufficient and needs to be dealt with immediately. We know that the mysql database data file directory cannot be changed dynamically, unlike oracle, which can directly change the data file directory. The following shows a better way to expand the mysql data file directory:

This set of libraries uses keepalived to provide a vip, and then the business connects to the vip (the vip here is 192. Please check the location of the vip below. It is now on S244, saying that I know the current S244.

[root@S244 ~] # ip a

1: lo: mtu 16436 qdisc noqueue state UNKNOWN

Link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00

Inet 127.0.0.1/8 scope host lo

Inet6:: 1/128 scope host

Valid_lft forever preferred_lft forever

2: eth0: mtu 1500 qdisc mq state UP qlen 1000

Link/ether 00:26:2d:0d:50:42 brd ff:ff:ff:ff:ff:ff

Inet 192.168.0.244/24 brd 192.168.0.255 scope global eth0

Inet 192.168.0.118/24 brd 192.168.0.255 scope global secondary eth0:1

Inet6 fe80::226:2dff:fe0d:5042/64 scope link

Valid_lft forever preferred_lft forever

3: eth2: mtu 1500 qdisc mq state UP qlen 1000

Link/ether 00:26:2d:0d:50:43 brd ff:ff:ff:ff:ff:ff

Inet6 fe80::226:2dff:fe0d:5043/64 scope link

Valid_lft forever preferred_lft forever

4: eth3: mtu 1500 qdisc pfifo_fast state DOWN qlen 1000

Link/ether 00:26:2d:0d:50:41 brd ff:ff:ff:ff:ff:ff

Inet 192.168.8.244/24 brd 192.168.8.255 scope global eth3

[root@S243 keepalived] # ip a

1: lo: mtu 16436 qdisc noqueue state UNKNOWN

Link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00

Inet 127.0.0.1/8 scope host lo

Inet6:: 1/128 scope host

Valid_lft forever preferred_lft forever

2: eth0: mtu 1500 qdisc mq state UP qlen 1000

Link/ether 00:26:2d:0d:50:42 brd ff:ff:ff:ff:ff:ff

Inet 192.168.0.244/24 brd 192.168.0.255 scope global eth0

Inet6 fe80::226:2dff:fe0d:5042/64 scope link

Valid_lft forever preferred_lft forever

3: eth2: mtu 1500 qdisc mq state UP qlen 1000

Link/ether 00:26:2d:0d:50:43 brd ff:ff:ff:ff:ff:ff

Inet6 fe80::226:2dff:fe0d:5043/64 scope link

Valid_lft forever preferred_lft forever

4: eth3: mtu 1500 qdisc pfifo_fast state DOWN qlen 1000

Link/ether 00:26:2d:0d:50:41 brd ff:ff:ff:ff:ff:ff

Inet 192.168.8.244/24 brd 192.168.8.255 scope global eth3

[root@S244 keepalived] #

All right, let's show the specific operation flow:

I intend to move the directory of one library to another path with plenty of space.

Before operating the database, you should communicate with the system engineering and stop the related services first.

1) operate the slave library first

1, turn off master-slave replication

Mysql > stop slave

2. Shut down the mysql service. Note that sometimes it is: service mysqld stop

[root@S243 ~] # service mysql stop

3. We know that a library corresponds to a subdirectory. After comparison, I decided to move the mailer library to another directory / mysql2

1) [root@S243 ~] # cd / mysql/datadir

2) [root@S243 datadir] # cp-r mailer / mysql2/mailer-copy the entire directory mailer to / mysql2, and name it mailer.

3) after confirming that the replication is successful, delete the mailer directory originally in / mysql

[root@S243 datadir] # rm-rf mailer

4) modify the subordinate group of mailer under / mysql2/

[root@S243 mysql2] # chown-R mysql:mysql mailer

5) establish a soft connection to the / mysql/datadir directory

[root@S243 mysql2] # ln-s / mysql2/mailer / mysql/datadir/mailer

4, start the database.

[root@S243 ~] # service mysql start

5. Enable master-slave replication and check the status of related processes. Yes is normal.

Mysql > start slave

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.244

Master_User: info_syncer

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.001358

Read_Master_Log_Pos: 643287297

Relay_Log_File: S243-relay-bin.001134

Relay_Log_Pos: 350

Relay_Master_Log_File: mysql-bin.001358

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

.

At this point, the operation of the library is over.

The operation for the master library is the same as the operation for the slave library, we need to pay attention to the mysql copied by the master and master. A vip 118is set up through keepalived, and the written business is logged into the database by connecting to vip. We can ensure that the written business will not be affected, because the server where vip is located is the main mysql. When this mysql dies, vip is automatically transferred to another server, still maintaining the normal write business.

Then I focus on the trigger conditions of vip transfer: we all know that the service monitored by keepalived dies, which will trigger vip migration, and that the suspension of keepalived services will also trigger vip drift.

1)。 Shutting down keepalived will trigger vip to float to another server

[root@S244 ~] # ip a

1: lo: mtu 16436 qdisc noqueue state UNKNOWN

Link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00

Inet 127.0.0.1/8 scope host lo

Inet6:: 1/128 scope host

Valid_lft forever preferred_lft forever

2: eth0: mtu 1500 qdisc mq state UP qlen 1000

Link/ether 00:26:2d:0d:50:42 brd ff:ff:ff:ff:ff:ff

Inet 192.168.0.244/24 brd 192.168.0.255 scope global eth0

Inet 192.168.0.118/24 brd 192.168.0.255 scope global secondary eth0:1

Inet6 fe80::226:2dff:fe0d:5042/64 scope link

Valid_lft forever preferred_lft forever

[root@S244 ~] # service keepalived stop

Check again, sure enough, there is no 118th vip.

[root@S244 ~] # ip a

1: lo: mtu 16436 qdisc noqueue state UNKNOWN

Link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00

Inet 127.0.0.1/8 scope host lo

Inet6:: 1/128 scope host

Valid_lft forever preferred_lft forever

2: eth0: mtu 1500 qdisc mq state UP qlen 1000

Link/ether 00:26:2d:0d:50:42 brd ff:ff:ff:ff:ff:ff

Inet 192.168.0.244/24 brd 192.168.0.255 scope global eth0

Inet6 fe80::226:2dff:fe0d:5042/64 scope link

Valid_lft forever preferred_lft forever

But when [root@S244] # service keepalived start again, vip118 floats back here again. It turns out that there is a parameter priority in the keepalived configuration file that is priority. As follows, it is found that the priority of s244 is higher than that of priority 50, and there is also a parameter nopreempt that means do not preempt vip if you have high priority. By default, preempt preempts vip.

[root@S243 keepalived] # cat keepalived.conf

Global_defs {

Notification_email {

}

}

Vrrp_instance VI_1 {

# state MASTER

State BACKUP

Interface eth0

Virtual_router_id 51

# priority 100

Priority 50

Mcast_src_ip 192.168.0.243

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}

Virtual_ipaddress {

# 202.85.218.197 dev eth2 label eth2:1

192.168.0.118/24 broadcast 192.168.0.255 dev eth0 label eth0:1

}

Virtual_server 192.168.0.118 3306 {

Delay_loop 2 # check real_server status every 2 seconds

Lb_algo wrr # LVS algorithm

Lb_kind DR # LVS mode

Persistence_timeout 60 # session duration

Protocol TCP

Real_server 192.168.0.243 3306 {

Weight 3

Script executed after notify_down / var/lib/mysql/mysql_kpa.sh # detected the service down

TCP_CHECK {

Connect_timeout 10 # connection timeout

Number of nb_get_retry 3 # reconnections

Delay_before_retry 3 # reconnection interval

Connect_port 3306 # Health check Port

}

}

[root@S244 ~] # cat / etc/keepalived/keepalived.conf

Global_defs {

Notification_email {

}

}

Vrrp_instance VI_1 {

# state BACKUP

State MASTER

Interface eth0

Virtual_router_id 51

# priority 50

Priority 100

Mcast_src_ip 192.168.0.244

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}

Virtual_ipaddress {

# 202.85.218.197 dev eth2 label eth2:1

192.168.0.118/24 broadcast 192.168.0.255 dev eth0 label eth0:1

}

Virtual_server 192.168.0.118 3306 {

Delay_loop 2 # check real_server status every 2 seconds

Lb_algo wrr # LVS algorithm

Lb_kind DR # LVS mode

Persistence_timeout 60 # session duration

Protocol TCP

Real_server 192.168.0.244 3306 {

Weight 3

Script executed after notify_down / var/lib/mysql/mysql_kpa.sh # detected the service down

TCP_CHECK {

Connect_timeout 10 # connection timeout

Number of nb_get_retry 3 # reconnections

Delay_before_retry 3 # reconnection interval

Connect_port 3306 # Health check Port

}

}

The spatial scalability of mysql database is still worse than that of oracle, so the preliminary preparation and estimation of mysql database is very important. You need to more accurately estimate the space growth for a period of time in the future, in order to reserve more accurate space, because mysql space expansion needs to be out of service, and there is a certain risk.

After reading the above, have you mastered how mysql can expand capacity by migrating the directory of a library? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.