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

There is a server online, in which there is a mysql database service, in fact, the library is also very small, just a few gigabytes, has been retained for many days of the backup set, but because of business, this database actually only has some basic data query, but the strange thing is that there is no database, has been a daily backup, retain nearly a week's backup set, this situation is also at peace. However, unfortunately, there is also an Oracle database deployed on this server, and the space is much larger. With the growth of the business, this amount of data has gone up. As a result, the use of space is becoming more and more tight. Keep the backup set for a week, and space is getting tighter and tighter. So a mysql slave library is created using gtid on an Oracle standby machine. This situation can basically meet the previous needs, so there is no need to do everything every day.

This situation did not last long, and one day I received the following alarm.

ZABBIX- monitoring system:

-

Alarm content: Free disk space is less than 20% on volume /

-

Alarm level: PROBLEM

-

Monitoring project: Free disk space on / (percentage): 1.94%

-

Alarm time: 2016.02.23-11:01:07

Boy, this partition actually uses the space of the root directory directly, so the space is even more tight.

At this point, you need to adjust the directory address of the data. Think about it, that is, adjust the address of datadir.

The first is to adjust the directory address of the data, modify / etc/my.cnf, and then stop the library. because of the space problem, there is no remaining space at last, as a result, the application of the library is directly hang, so it takes some time to stop the library directly.

# / etc/init.d/mysql stop

Shutting down MySQL (Percona Server)... SUCCESS!

The following error was reported when opening the library.

# / etc/init.d/mysql start

Starting MySQL (Percona Server). ERROR! The server quit without updating PID file (/ U01/app/mysql_3306/mysql.pid).

After some investigation, it was found that it was the problem of the directory permissions of the files.

If you continue to start after repair, it will still report the same error.

I had no idea for a while, so I tested it, changed the file directory back to the original path, modified the path inside / etc/my.cnf, and started the library again. At this time, I started to accept application logs from the library, and some expired binlog were deleted. After catching up with the library, it will be soon to stop the library again.

# / etc/init.d/mysql stop

Shutting down MySQL (Percona Server)... SUCCESS!

However, after migrating the files, it is still the same problem to start the library again after modifying / etc/my.cnf.

[root@shadoop app] # / etc/init.d/mysql start

Starting MySQL (Percona Server). ERROR! The server quit without updating PID file (/ U01/app/mysql_3306/mysql.pid).

Check error.log and find the following paragraph, the same as before, but there is a new discovery.

160223 11:59:38 mysqld_safe mysqld from pid file / U01/app/mysql_3306/mysql.pid ended

160223 11:59:56 mysqld_safe Starting mysqld daemon with databases from / U01/app/mysql_3306

2016-02-23 11:59:56 21600 [Note] Plugin 'FEDERATED' is disabled.

2016-02-23 11:59:56 21600 [Note] InnoDB: The InnoDB memory heap is disabled

2016-02-23 11:59:56 21600 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2016-02-23 11:59:56 21600 [Note] InnoDB: Compressed tables use zlib 1.2.3

2016-02-23 11:59:56 21600 [Note] InnoDB: Using Linux native AIO

2016-02-23 11:59:56 21600 [Note] InnoDB: Using CPU crc32 instructions

2016-02-23 11:59:56 21600 [Note] InnoDB: Initializing buffer pool, size = 4.0G

2016-02-23 11:59:57 21600 [Note] InnoDB: Completed initialization of buffer pool

2016-02-23 11:59:57 21600 [Note] InnoDB: Highest supported file format is Barracuda.

2016-02-23 11:59:57 21600 [Note] InnoDB: 128rollback segment (s) are active.

2016-02-23 11:59:57 21600 [Note] InnoDB: Waiting for purge to start

2016-02-23 11:59:57 21600 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.14-rel62.0 started; log sequence number 278581

8494

2016-02-23 11:59:57 7ffa261f0700 InnoDB: Loading buffer pool (s) from. / / ib_buffer_pool

^ G/usr/sbin/mysqld: File'/ home/mysql_3306/mysql-bin.000006' not found (Errcode: 2-No such file or directory)

2016-02-23 11:59:57 21600 [ERROR] Failed to open log (file'/ home/mysql_3306/mysql-bin.000006', errno 2)

2016-02-23 11:59:57 21600 [ERROR] Could not open log file

2016-02-23 11:59:57 21600 [ERROR] Can't init tc log

2016-02-23 11:59:57 21600 [ERROR] Aborting

Is that mysql will try to find a binlog / home/mysql_3306/mysql-bin.000006.

Where is this part of the information?

# less relay-index.index

/ home/mysql_3306/mysql-relay.000008

/ home/mysql_3306/mysql-relay.000009

/ U01/app/mysql_3306/mysql-relay.000010

/ U01/app/mysql_3306/mysql-relay.000011

With fresh energy, I manually modified the file to see if it could take effect.

Modified to:

# vi relay-index.index

/ U01/app/mysql_3306/mysql-relay.000008

/ U01/app/mysql_3306/mysql-relay.000009

/ U01/app/mysql_3306/mysql-relay.000010

/ U01/app/mysql_3306/mysql-relay.000011

Then try change master to resynchronize it based on the latest point in time.

> change master to master_host='10.127.0.xx',master_port = 3306 mastery usernames replicated, mastery passwordbacks, slaveusernames, masterpieces autopositions position1

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

The following error is reported when starting slave.

> start slave

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

After rebooting, continue to try start slave and find that the error remains the same.

At this time, the only way is reset slave.

> start slave

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

> reset slave

Query OK, 0 rows affected (0.00 sec)

> change master to master_host='10.127.0.xx',master_port = 3306 mastery usernames replicated, mastery passwordbacks, slaveusernames, masterpieces autopositions position1

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

> start slave

Query OK, 0 rows affected (0.01 sec)

Check again that slave has been leveled with the log of the main database.

> show slave status\ G

* *

Replicate_Ignore_Server_Ids:

Master_Server_Id: 200

Master_UUID: 170281bc-1957-11e4-ad6e-842b2b4841e9

Master_Info_File: / U01/app/mysql_3306/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Reset slave causes slave to forget the location information of the master-slave replication relationship. This statement deletes the master.info file and relay-log.info file as well as all relaylog files and re-enables a new relaylog file.

The replication process must be stopped using the stop slave command before using reset slave, and all relay log will be deleted regardless of whether they are fully applied by the SQL thread process or not.

But none of this matters if the delay is small. After all, it is much better to solve this problem than to receive an alarm every three or five times.

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.