|
安装环境说明 系统环境： [iyunv@~]# cat /etc/redhat-release CentOS release 6.5 (Final) [iyunv@~]# uname -r 2.6.32-431.el6.x86_64 数据库： 由于是模拟环境，主从库在同一台服务器上，服务器IP地址192.168.1.7 主库使用3306端口 从库使用3307端口 数据库数据目录/data 安装MySQL数据库服务下载软件包 今天我们是用二进制安装包进行布署MySQL数据库服务，其它方式的安装布署方法请参考前面的文章 创建数据目录、软件安装目录 [iyunv@~]#mkdir /data｛3306，3307｝ -p [iyunv@~]#mkdri /application 解压软件 [iyunv@~]#tar zxf mysql-5.5.51-linux2.6-x86_64.tar.gz [iyunv@~]#mv mysql-5.5.51-linux2.6-x86_64 /application/mysql-5.5.51 [iyunv@~]#ln -s /application/mysql-5.5.51 /application/mysql 创建用户 [iyunv@~]#groupadd mysql [iyunv@~]#useradd -g mysql -M mysql 初始化数据库 [iyunv@~]#/application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql [iyunv@~]#/application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/data/3307/data --user=mysql 创建配置文件 [iyunv@~]#vi /data/3306/my.cnf [client] port = 3306 socket = /data/3306/mysql.sock [mysql] no-auto-rehash [mysqld] user = mysql port = 3306 socket = /data/3306/mysql.sock basedir = /application/mysql 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 = 192K 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 #主从同步的关键点，从库上不需要开启 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,1062 replicate-ignore-db=mysql server-id = 1 #主库从库ID 不可相同 [mysqldump] quick max_allowed_packet = 2M [mysqld_safe] log-error=/data/3306/mysql3306.err pid-file=/data/3306/mysqld.pid
数据库启动脚本： [iyunv@~]#vi /data/3306/mysql #!/bin/sh port=3306 user="root" pwd="123456" Path="/application/mysql/bin" 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 备注：主从库配置文件与启动文件一样，只需修改端口与server-id即可完成配置 授权目录并增加启动文件可执行权限
[iyunv@~]#chown -R mysql.mysql /data [iyunv@~]#find /data -name mysql -exex chmod +x {} \; 启动数据库 [iyunv@~]#/data/3306/mysql start [iyunv@~]#/data/3307/mysql start 修改默认数据库密码 [iyunv@~]#mysqladmin -uroot password '123456' -S /data/3306/mysql.sock [iyunv@~]#mysqladmin -uroot password '123456' -S /data/3307/mysql.sock 测试登陆，可以登陆两个数据库即可完成全部安装过程 配置主库1）备份主库 mkdir /backup
登陆主库创建用步同户并授权 [iyunv@~]#mysql -uroot -p123456 -S /data/3306/mysql.sock mysql> grant replication slave on *.* to rep@'192.168.1.%' identified by'123456'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) 执行锁表操作 [iyunv@~]#/application/mysql/bin/mysql -uroot -p123456 -S /data/3306/mysql.sock -e "flush table with read lock;" 备份主库 [iyunv@~]#/application/mysql/bin/mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show master status;" >/backup/mysql.log [iyunv@~]#/application/mysql/bin/mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B |gzip >/backup/mysql.sql.gz 解除锁表状态 [iyunv@~]#/application/mysql/bin/mysql -uroot -p123456 -S /data/3306/mysql.sock -e "unlock tables;" 备注：以上操作也可以登陆主库进行，但是需要注意的是，执行锁表操作后，需要另开启一个窗口进行数据备份，不可直接退出，防止有数据写入导致备份的数据不完整。最好是使用非交互式操作。 配置从库实现主从同步将主库的备份文件解压并恢复数据库 [iyunv@backup ]#gzip -d mysql.sql.gz [iyunv@backup ]#/application/mysql/bin/mysql -uroot -p123456 -S /data/3307/mysql.sock < mysql.sql 查看LOG日志 [iyunv@backup ]#cat mysql.log +------------------+----------+--------------+-----------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +----------+----------+--------------+------------------+ | mysql-bin.000002 | 424 | | | +----------+----------+--------------+------------------+ 登陆从库执行下面的操作 mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.1.7', #服务器IP -> MASTER_PORT=3306, #主库端口 -> MASTER_USER='rep', #同步的用户 -> MASTER_PASSWORD='123456', #同步的用户密码 -> MASTER_LOG_FILE=' mysql-bin.000002', #binlog文件 -> MASTER_LOG_POS=424; #位置点 mysql> start slave; #开启同步 等待60S后查看同步状态 [iyunv@backup ]# mysql -S /data/3307/mysql.sock -e "show slave status\G"|egrep "Seconds_Behind_Master|_Running" Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 只要出现上述情况说明主从同步成功 测试主从同步主库创建一个数据库 [iyunv@backup ~]# mysql -S /data/3306/mysql.sock -e "create database tongbuku" [iyunv@backup ~]# mysql -S /data/3306/mysql.sock -e "show databases" +-----------------------------+ | Database | +-----------------------------+ | information_schema | | mysql | | performance_schema | | test | | tongbuku | +-----------------------------+ 查看从库同步情况 [iyunv@backup ~]# mysql -S /data/3307/mysql.sock -e "show databases" +-----------------------------+ | Database | +-----------------------------+ | information_schema | | mysql | | performance_schema | | test | | tongbuku | +-----------------------------+
| 
QQ群⑧：
窥视卡
雷达卡
发表于 2017-3-6 10:08:28
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡