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

A foreplay

# installation

Yum install-y mysql-server mysql mysql-devel

# different instance directories

Mkdir / data/ {3306, 3307}-p

# Licensing

Chown-R mysql.mysql / data

Configuration file and startup

# delete or rename the original / etc/my.cnf configuration file

Mv / etc/my.cnf / etc/my.cnf.bak

# create a configuration file vi / data/3306/my.cnf (the following is the content of the configuration file)

# configuration files start here

[client]

Port = 3306

Socket = / data/3306/mysql.sock

[mysql]

No-auto-rehash

[mysqld]

User = mysql

Port = 3306

Socket = / data/3306/mysql.sock

Basedir = / usr # compilation and binary installation fill in the path according to the actual directory (/ usr is installed by yum)

Datadir = / data/3306/data

Open_files_limit = 1024

Back_log = 600,

Max_connections = 800,

Max_connect_errors = 3000

Table_cache = 614

External-locking = FALSE

Max_allowed_packet = 8m

Sort_buffer_size = 1m

Join_buffer_size = 1m

Thread_cache_size = 100

Thread_concurrency = 2

Query_cache_size = 2m

Query_cache_limit = 1m

Query_cache_min_res_unit = 2k

Thread_stack = 19K

Tmp_table_size = 2m

Max_heap_table_size = 2m

Long_query_time = 1

Pid-file = / data/3306/mysql.pid

Log-bin = / data/3306/mysql-bin # the key point of master-slave synchronization, which does not need to be enabled on the slave library

Relay-log = / data/3306/relay-bin

Relay-log-info-file = / data/3306/relay-log.info

Binlog_cache_size = 1m

Max_binlog_cache_size = 1m

Max_binlog_size = 2m

Expire_logs_days = 7

Key_buffer_size = 16m

Read_buffer_size = 1m

Read_rnd_buffer_size = 1m

Bulk_insert_buffer_size = 1m

Lower_case_table_names = 1

Skip-name-resolve

Slave-skip-errors = 1032 and 1062

Replicate-ignore-db=mysql

Server-id = 1 # Master library slave library ID is not the same

[mysqldump]

Quick

Max_allowed_packet = 2m

[mysqld_safe]

Log-error=/data/3306/mysql3306.err

Pid-file=/data/3306/mysqld.pid

# configuration file ends here

= =

3 create startup script vi / data/3306/mysql

# the startup script starts here

#! / bin/sh

Port=3306

User= "root"

Pwd= "123456" # enter according to the actual password

Path= "/ usr/bin" # specifies the path of the mysql executable program according to the actual installation (can be looked up by which mysql)

Sock= "/ data/$ {port} / mysql.sock"

Start_mysql ()

{

If [!-e "$sock"]; then

Printf "Starting MySQL...\ n"

/ bin/sh ${Path} / mysqld_safe-- defaults-file=/data/$ {port} / my.cnf 2 > & 1 > / dev/null &

Else

Printf "MySQL is running...\ n"

Exit

Fi

}

Stop_mysql ()

{

If [!-e "$sock"]; then

Printf "MySQL is stopped...\ n"

Exit

Else

Printf "Stoping MySQL...\ n"

${Path} / mysqladmin-u ${user}-p$ {pwd}-S / data/$ {port} / mysql.sock shutdown

Fi

}

Restart_mysql ()

{

Printf "Restarting MySQL...\ n"

Stop_mysql

Sleep 2

Start_mysql

}

Case $1 in

Start)

Start_mysql

Stop)

Stop_mysql

Restart)

Restart_mysql

*)

Printf "Usage: / data/$ {port} / mysql {start | stop | restart}\ n"

Esac

# this ends the startup script

Chmod + x / data/3306/mysql # Authorization executable

=

4 create the same copy for 3307, and pay attention to replacing all 3306 with 3307 and modifying different server-id

Cp / data/3306/mysql / data/3307/

Cp / data/3306/my.cnf / data/3307/

Sed-I 's/server-id = 1/server-id = 2max g' / data/3307/my.cnf

Sed-I's Universe 3306 + 3307 / data/3307/my.cnf

Sed-I's Universe 3306 + 3307 / data/3307/mysql

3 initialize 2 instances

# initialize port 3306 database

Mysql_install_db-datadir=/data/3306/data-user=mysql

# start 3306 database

/ data/3306/mysql start

# modify the login password of 3306 instance

Mysqladmin-uroot password '123456'-S / data/3306/mysql.sock

Mysql_install_db-datadir=/data/3307/data-user=mysql

/ data/3307/mysql start

Mysqladmin-uroot password '123456'-S / data/3307/mysql.sock

Four configurations of the main library

# Log in to 3306 instance (main library)

Mysql-uroot-p123456-S / data/3306/mysql.sock

# account password for authorization synchronization

Grant replication slave on *. * to rep@'%' identified by'66888888'

# Refresh permissions

Flush privileges

# exit

Exit

# main database lock table

Mysql-uroot-p123456-S / data/3306/mysql.sock-e "flush tables with read lock;"

# check the binlog status of the main library

Mysql-uroot-p123456-S / data/3306/mysql.sock-e "show master status;" > / mysql.log

# back up the main library

Mysqldump-uroot-p123456-S / data/3306/mysql.sock-A-B | gzip > / mysql.sql.gz

# unlock the main library

Mysql-uroot-p123456-S / data/3306/mysql.sock-e "unlock tables;"

Note: do not operate the master server MYSQL after performing this step to prevent the status value of the master server from changing

Five configurations from the library

# Import backup of the main database

Gzip-d / mysql.sql.gz

Mysql-uroot-pendant 123456'-S / data/3307/mysql.sock

< /mysql.sql mysql -uroot -p'123456' -S /data/3307/mysql.sock #设置同步信息 mysql>

CHANGE MASTER TO

-> MASTER_HOST='192.168.1.7', # Server IP

-> MASTER_PORT=3306, # main library port

-> MASTER_USER='rep', # synchronized users

-> MASTER_PASSWORD='66888888', # synchronized user password

-> MASTER_LOG_FILE='mysql-bin.000002', # binlog file (this information is in / mysql.log)

-> MASTER_LOG_POS=424; # location point (this information is in / mysql.log)

Note: you can set the above information change master to master_host='192.168.145.222',master_user='mysync',master_password='q123456' at one time.

Master_log_file='mysql-bin.000004',master_log_pos=308

# enable slave mode

Mysql > start slave

# View status

Mysql-uroot-pendant 123456'-S / data/3307/mysql.sock-e "show slave status\ G" | egrep "Seconds_Behind_Master | _ Running"

# 2 Yes indicates success. If not, there may be problems with account synchronization settings in the previous step at most.

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.