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

1. Requirements: migrate from mysql of 11 servers to 12 servers

For 12 servers:

Nc-l 12345 | tar zxvf

For 11 servers:

Ldd / usr/local/mysql/bin/mysqld | xargs tar zcvf so.tar.gz / / View dependent library files with ldd and package them

Scp so.tar.gz 192.168.1.12:/root// remote transfer library file

Tar zcvf-mysql | nc 192.168.1.12 12345

For 12 servers: / / above I use the nc command to transmit, as long as it can be transmitted.

Iptables-F

Setenforce 0DB / pay attention to turn off the security mechanism to ensure file transfer

Tar xf so.tar.gz// put the lib64, so I won't write it in detail here.

Mv mysql/ / usr/local/// has been transferred and moved to the usr directory

Cd / usr/local/mysql/support-files/

Cp my-medium.cnf / etc/my.cnf// copy medium profile

Cp mysql.server / etc/init.d/mysqld// replication startup file

Ln-s / usr/local/mysql/bin/* / usr/local/bin// copy the mysql command, of course, add it with PAHT

Cd / usr/local/mysql/scripts/

Useradd-M-s / sbin/nologin mysql// creates mysql program users before data initialization

. / mysql_install_db-- basedir=/usr/local/mysql/-- datadir=/usr/local/mysql/data-- user=mysql// data initialization

Service mysqld start// starts the mysqld service

Ps aux | grep mysql

Mysql-uroot-pram / login, user name password is the same as 11 server, data is the same

2. Requirements: a mysql server starts mysql with multiple ports

[root@localhost ~] # service mysqld stop

[root@localhost] # for i in {3306.. 3308}; do cp / etc/my.cnf / etc/my$ {I} .cnf; done

/ / copy the configuration file with the name / etc/my3307.cnf

[root@localhost] # mkdir-p / www/mysqldata {3306.. 3308}

/ / make a data directory

[root@localhost /] # for i in {3306. 3308}; do / usr/local/mysql/scripts/mysql_install_db-- datadir=/www/mysqldata$i-- user=mysql-- basedir=/usr/local/mysql;done

/ / initialization of a pair of created data directories

[root@localhost local] # vi / etc/my3307.cnf / / change two items: take 3307 as an example

[mysqld]

The item port=3307// has been changed.

Socket=/tmp/mysql_3307.sock / / specify the socket directory

Datadir=/www/mysqldata3307/ specify the data directory. If not, you need to add-- datadir=/www/mysqldata3307-user=mysql when starting port 3307 below.

[client]

Port = 3307

Socket = / tmp/mysql_3307.sock

Start port 3307: (other similar)

Mysqld_safe-- defaults-extra-file=/etc/my3307.cnf &

Enter mysql:

[root@localhost local] # mysql-uroot-S / tmp/mysql_3307.sock (you can log in without-root)

Shut down the corresponding mysqld service: (there are many ways, you can also use kill-9)

[root@localhost local] # mysqladmin-uroot-S / tmp/mysql_3307.sock shutdown

3307 of the data can be copied directly to 3306:

[root@localhost test] # cp aa.frm.. /.. / mysqldata3306/test/

/ / currently under mysqldata3307/test

3. Requirements: use innobackupex tools to back up data

Multi-port mode is enabled. For port 3306, the configuration file is / etc/my3306.cnf data file is / www/mysqldata3306

[root@localhost ~] # yum-y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL// installation dependency package

[root@localhost] # rpm-ivh percona-xtrabackup-2.0.2-461.rhel6.x86_64.rpm-- nodeps

[root@localhost tmp] # mkdir / home/mysqlbak/fullfile/aaa-p / / make backup file storage path

[root@localhost tmp] # innobackupex-- user=root-- slave-info-- socket=/tmp/mysql3306.sock-- defaults-file=/etc/my3306.cnf / home/mysqlbak/fullfile/aaa > / dev/null// start backup

Add-databases "mysql.user test" to specify the backup of the specified table in the library

Close the database before backup and delete data files and log files (just rename it)

Mysqladmin-uroot-S / tmp/mysql33036.sock shutdown / / shut down the database

[root@localhost www] # mv mysqldata3306 mysqldata3306.bak// deletes the original data file

[root@localhost www] # mkdir mysqldata3306// to create empty directory data files

[root@localhost tmp] # innobackupex-- defaults-file=/etc/my3306.cnf-- user=root-- password=123-- apply-log-- rsync-- use-memory=4 / home/mysqlbak/fullfile/aaa/2017-03-06 upload 13-08-44max / Application Log,-- use-memory=4 option can speed up recovery

[root@localhost tmp] # innobackupex-- defaults-file=/etc/my3306.cnf-- user=root-- password=123-- copy-back / home/mysqlbak/fullfile/aaa/2017-03-06 / 13-08-44 / / copy to the original data file

[root@localhost www] # chown-R mysql:mysql mysqldata3306// gives permission to mysql to read

From what can be seen, recovery is divided into two steps, the first step is apply-log, in order to speed up, it is generally recommended to set-- use-memory, after this step is completed, the backup files under the directory / backup/mysql/data/2017-03-06 13-08-44 are ready.

The second step is copy-back, which copies the backup files to the original data directory.

After the restore is complete, be sure to check that the owner and permissions of the data directory are correct.

Common options:

-- parallel=4-- the number of throttle=400 in parallel. Select the appropriate one according to the host configuration. The default is 1, and multiple can speed up the backup.

-- stream=tar compression type. Tar format is selected here, which can be added or not. With the addition of the file, it will be smaller, and it will be packed at the time of backup.

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.