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

The following content mainly brings you the master-slave building method of mysql. The knowledge mentioned is slightly different from books. It is summed up by professional and technical personnel in the process of contact with users. It has a certain value of experience sharing and hopes to bring help to the broad masses of readers.

After the master database is built: 1. Packaged backup data (1) read-only to the database lock table > flush table with read lock; (2) time to automatically unlock by default > show variables like'% timeout%'; (3) to record the status information of the master database after locking the table. > show master status is needed when connecting to the master database from the slave library.

Or

# mysql-uroot-p123456-S/data/3306/mysql.sock-e "show master status" (4) Open a new window after locking the table, with a large amount of data (more than 50G), allow shutdown and package # mkdir-p / server/backup (5) package data # mysqldump-uroot-p123456-S/data/3306/mysql.sock-events\-A-B | gzip > / server/backup/mysql_bak.$ (date +% F) .sql.gz (6) unlock database # unlock tables

Build from the library

1. Install the dependent packages and compiling software required by MySQL

(1) install the dependency packages required by MySQL

# yum install ncurses-devel libaio-devel-y

(2) install the software needed to compile MySQL

# tar xf cmake-2.8.8.tar.gz#cd cmake-2.8.8#./configure#gmake & & gmake install

Or install directly with yum

# yum-y install cmake

2. Start installing MySQL database

(1) establish MySQL users

# useradd-s / sbin/nologin-M mysql

(2) install MySQL by compiling

# tar xf mysql-5.5.49.tar.gz#cd mysql-5.5.49#cmake. -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.49\-DMYSQL_DATADIR=/application/mysql-5.5.49/data\-DMYSQL_UNIX_ADDR=/application/mysql-5.5.49/tmp/mysql.sock\-DDEFAULT_CHARSET=utf8\-DDEFAULT_COLLATION=utf8_general_ci\-DEXTRA_CHARSETS=gbk,gb2312,utf8 Ascii\-DENABLED_LOCAL_INFILE=ON\-DWITH_INNOBASE_STORAGE_ENGINE=1\-DWITH_FEDERATED_STORAGE_ENGINE=1\-DWITH_BLACKHOLE_STORAGE_ENGINE=1\-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1\-DWITHOUT_PARTITION_STORAGE_ENGINE=1\-DWITH_FAST_MUTEXES=1\-DWITH_ZLIB=bundled\-DENABLED_LOCAL_INFILE=1\-DWITH_EMBEDDED_SERVER=1\-DWITH_DEBUG=0#make & & make install#ln-s / application/mysql-5.5.49/ / application/mysql

(3) configure the slave configuration file

# mkdir-p / data/3307/data#vim / data/3307/ my.cnf [client] port = 3307socket = / data/3307/ MySQL. Socks [MySQL] no-auto- rehash [mysqld] user = mysqlport = 3307socket = / data/3307/mysql.sockbasedir = / application/mysqldatadir = / data/3307/dataopen_files_limit = 1024back_log = 600max_connections = 800max_connect_errors = 3000table_cache = 614external-locking = FALSEmax_allowed_packet = 8Msort_buffer_size = 1Mjoin_buffer_size = 1Mthread_cache_size = 100thread_concurrency = 2query_cache_size = 2Mquery_cache_limit = 1Mquery_cache_min_res_unit = 2k#default_table_type = InnoDBthread_stack = 192K#transaction_isolation = READ-COMMITTEDtmp_table_size = 2Mmax_heap_table_size = 2Mlong_query_time = 1#log_long_format#log-error = / data/3307/error.log#log-slow-queries = / data/3307/slow.logpid-file = / data/3307 / mysql.pidlog-bin = / data/3307/mysql-binrelay-log = / data/3307/relay-binrelay-log-info-file = / data/3307/relay-log.infobinlog_cache_size = 1Mmax_binlog_cache_size = 1Mmax_binlog_size = 2Mexpire_logs_days = 7key_buffer_size = 16Mread_buffer_size = 1Mread_rnd_buffer_size = 1Mbulk_insert_buffer_size = 1M#myisam_sort_buffer_size = 1M#myisam_max_sort_file_size = 10G# Myisam_max_extra_sort_file_size = 10G#myisam_repair_threads = 1#myisam_recoverlower_case_table_names = 1skip-name-resolveslave-skip-errors = 1032 Magazine 1062replicatelichedqlservercolorid = 3innodb_additional_mem_pool_size = 4Minnodb_buffer_pool_size = 32Minnodb_data_file_path = 4innodb_thread_concurrency = 8innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 2Minnodbaked log files _ Size = 4Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 120innodb_file_per_table = 0 [mysqldump] quickmax_allowed_packet = 2m [mysqld _ safe] log-error=/data/3307/mysql_3307.errpid-file=/data/3307/mysqld.pid

(4) configure the boot file from the library

# vim / Datagram 3307 max mysqlhammer binql cards # # initport=3307mysql_user= "root" mysql_pwd= "123456" CmdPath= "/ application/mysql/bin" mysql_sock= "/ data/$ {port} / mysql.sock" # startup functionfunction_start_mysql () {if [!-e "$mysql_sock"] Then printf "Starting MySQL...\ n" / bin/sh$ {CmdPath} / mysqld_safe-- defaults-file=/data/$ {port} / my.cnf 2 > & 1 > / dev/null & else printf "MySQL isrunning...\ n" exit fi} # stop functionfunction_stop_mysql () {if [!-e "$mysql_sock"] Then printf "MySQL isstopped...\ n" exit else printf "StopingMySQL...\ n" ${CmdPath} / mysqladmin-u ${mysql_user}-p$ {mysql_pwd}-S / data/$ {port} / mysql.sock shutdown fi} # restart functionfunction_restart_mysql () {printf "RestartingMySQL...\ n" function_stop_mysql sleep 2 function_start_mysql} case $1 instart) function_start_mysql Stop) function_stop_mysql;;restart) function_restart_mysql;;*) printf "Usage:/data/$ {port} / mysql {start | stop | restart}\ n" esac

(5) modify file permissions

# chown-R mysql.mysql / data#chmod 700 / data/3307/mysql

Configure the global variable # echo 'export PATH=/application/mysql/bin:$PATH' > > / etc/profile#source / etc/profile initialization database # cd / application/mysql/scripts#./mysql_install_db--basedir=/application/mysql-- datadir=/data/3307/data-- user=mysql startup database # / data/3307/mysql start login database # mysql-S / data/3307/mysql.sock set login password # mysqladmin-u root-S / data/3307/ Mysql.sockpassword '123456'#mysql-uroot-p-S / data/3307/mysql.sock launch # find / data-type f-name "mysql" | xargschmod 700#find / data-type f-name "mysql"-execchown root.root {}\ Set up self-boot # echo "# mysql multi instances" > > / etc/rc.local#echo "/ data/3307/mysql start" > > / etc/rc.local login database # mysql-uroot-p123456-S / data/3307/mysql.sock#show variables like 'server_id'; to delete unwanted libraries # select user,host from mysql.user;#drop user' root'@'::1';#drop user'@ 'db02';#drop user' root'@'db02';#drop user''@ 'localhost' # flush privileges; slave library connection master library configuration information

Log in to execute from the library

> CHANGE MASTER TO > MASTER_HOST='10.0.0.51', MASTER_PORT=3306, MASTER_USER='rep', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=729

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.