接上一篇文章：MySQL数据库备份介绍数据库安装环境说明系统环境：[root@~]#cat/etc/redhat-releaseCentOSrelease6.5(Final)[root@~]#uname-r2.6.32-431.el6.x86_64database:因为是模拟环境，主从库在同一台服务器上，服务器IP地址为192.168.1.7主库使用端口3306和从库使用3307端口数据库数据目录/data安装MySQL数据库服务下载软件包今天我们使用二进制安装包来部署MySQL数据库服务。其他安装部署方法可以参考之前的文章[root@~]#wgethttp://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.51-linux2.6-x86_64.tar。gz创建数据目录，软件安装目录[root@~]#mkdir/data{3306,3307}-p[root@~]#mkdri/application解压软件[root@~]#tarzxfmysql-5.5.51-linux2.6-x86_64.tar.gz[root@~]#mvmysql-5.5.51-linux2.6-x86_64/application/mysql-5.5.51[root@~]#ln-s/application/mysql-5.5.51/application/mysql创建用户[root@~]#groupaddmysql[root@~]#useradd-gmysql-Mmysql初始化数据库[root@~]#/application/mysql/scripts/mysql_install_db--basedir=/application/mysql--datadir=/data/3306/data--user=mysql[root@~]#/application/mysql/scripts/mysql_install_db--basedir=/application/mysql--datadir=/data/3307/data--user=mysql创建配置文件[root@~]#vi/data/3306/my.cnf[client]port=3306socket=/data/3306/mysql.sock[mysql]no-auto-rehash[mysqld]user=mysqlport=3306socket=/data/3306/mysql.sockbasedir=/application/mysqldatadir=/data/3306/dataopen_files_limit=1024back_log=600max_connections=800max_connect_errors=3000table_cache=614external-locking=FALSEmax_allowed_pa??cket=8Msort_buffer_size=1Mjoin_buffer_size=1Mthread_cache_size=100thread_concurrency=2query_cache_size=2Mquery_cache_limit=1Mquery_cache_min_res_unit=2kthread_stack=192Ktmp_table_size=2Mmax_heap_table_size=2Mlong_query_time=1pid-file=/data/3306/mysql.pidlog-bin=/data/3306/mysql-bin#主要从同步的关键点，从库上不需要启动relay-log=/data/3306/relay-binrelay-log-info-file=/data/3306/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=1Mlower_case_table_names=1skip-name-resolveslave-skip-errors=1032,1062replicate-ignore-db=mysqlserver-id=1#主库从库ID不可相同[mysqldump]quickmax_allowed_pa??cket=2M[mysqld_safe]log-error=/data/3306/mysql3306.errpid-file=/data/3306/mysqld.pid数据库启动脚本：[root@~]#vi/data/3306/mysql#!/bin/shport=3306user="root"pwd="123456"Path="/application/mysql/bin"sock="/data/${port}/mysql.sock"start_mysql(){如果[!-e"$sock"];thenprintf"StartingMySQL...\n"/bin/sh${Path}/mysqld_safe--defaults-file=/data/${port}/my.cnf2>&1>/dev/null&elseprintf"MySQLisrunning...\n"exitfi}stop_mysql(){if[!-e"$sock"];thenprintf"MySQLisstopped...\n"exitelseprintf"StopingMySQL...\n"${Path}/mysqladmin-u${user}-p${pwd}-S/data/${port}/mysql.sockshutdownfi}restart_mysql(){printf"RestartingMySQL...\n"stop_mysqlsleep2start_mysql}case$1instart)start_mysql;;stop)stop_mysql;;restart)restart_mysql;;*)printf"Usage:/data/${port}/mysql{start|stop|restart}\n"esac注：主从数据库配置文件同启动文件，只需要修改端口和server-id即可完成配置授权目录，增加启动文件的可执行权限[root@~]#chown-Rmysql.mysql/data[root@~]#find/data-namemysql-exexchmod+x{}\;启动数据库[root@~]#/data/3306/mysqlstart[root@~]#/data/3307/mysqlstart修改默认数据库密码[root@~]#mysqladmin-urootpassword'123456'-s/data/3306/mysql.sock[root@~]#mysqladmin-urootpassword'123456'-S/data/3307/mysql.sock测试登录，可以登录两个数据库完成整个安装过程配置主库1）备份主库mkdir/backup登录主库创建用户同步用户并授权[root@~]#mysql-uroot-p123456-S/data/3306/mysql.sockmysql>grantreplicationslaveon*.*torep@'192.168.1.%'identifiedby'123456';QueryOK,0rowsaffected(0.00sec)mysql>flushprivileges;QueryOK,0rowsaffected(0.00sec)执行锁表操作[root@~]#/application/mysql/bin/mysql-uroot-p123456-S/data/3306/mysql.sock-e"flushtablewithreadlock;"备份主库[root@~]#/application/mysql/bin/mysql-uroot-p123456-S/data/3306/mysql.sock-e"显示主人状态;">/backup/mysql.log[root@~]#/application/mysql/bin/mysqldump-uroot-p123456-S/data/3306/mysql.sock-A-B|gzip>/backup/mysql.sql.gz解锁表状态[root@~]#/application/mysql/bin/mysql-uroot-p123456-S/data/3306/mysql.sock-e"unlocktables;"备注：以上操作也可以通过登录主数据库进行，但需要注意的是，执行锁表操作后，需要另外打开一个窗口进行数据备份，不能直接退出。最好防止因数据写入导致备份数据不完整使用非交互操作。配置从库实现主从同步解压主库备份文件，恢复数据库[root@backup]#gzip-dmysql.sql.gz[root@backup]#/application/mysql/bin/mysql-uroot-p123456-s/data/3307/mysql.sock
