MySQL基本运维操作指南, 纯工具文
为了方便区分 mysql> 表示为MySQL命令行模式，其他为shell终端模式
彻底删除mysql
yum remove mysql mysql-server mysql-libs compat-mysql51
rm -rf /var/lib/mysql
rm /etc/my.cnf
rpm -qa | grep mysql # 查看是否还有mysql
yum安装依赖包
yum -y install cmake make gcc gcc-c++ ncurses-devel bison openssl-devel
添加MySQL所需要的用户组
groupadd mysql
useradd -g mysql -r mysql
下载MySQL源码包
wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.36.tar.gz
创建MySQL安装所需的目录
mkdir /data/mysql/{data,tmp,logs} -pv
chown mysql.mysql -R /data/mysql/ # 修改权限
解压编译安装MySQL
tar xf mysql-5.5.36.tar.gz
cd mysql-5.5.36
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/data/mysql/data \
-DSYSCINFDIR=/etc \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWARP=0 \
-DWITH_UNIX_ADDR=/tmp/mysql.sock \
-DDEFAULT_CHARASET=uft8 \
-DDEFAULT_COLLATTON=utf9_general_ci
make && make install
为MySQL提供启动脚本
cp support-files/mysql.server /etc/rc.d/init.d/mysqld
为master-01主库提供配置文件
[client] port = 3306 socket = /var/lib/mysql/mysql.sock default-character-set = utf-8 [mysqld] server-id = 1 port = 3306 user = mysql basedir = /usr/local/mysql datadir = /data/mysql/data tmpdir = /data/mysql/tmp socket = /var/lib/mysql/mysql.sock #skip-external-locking #skip-name-resolve default-storage-engine = INNODB character-set-server = utf8 wait-timeout = 100 connect_timeout = 20 interactive_timeout = 100 back_log = 300 #myisam_recover #event_scheduler = on log-bin=/data/mysql/logs/mysql-bin binlog_format = row max_binlog_size = 8M #binlog_cache_size = 1M #slave-net-timeout = 10 #skip-slave-start #slow_query_log= 1 #long_query_time = 1 slow_query_log_file = /data/mysql/logs/mysql.slow log-error = /data/mysql/logs/error.log max_connections = 128 #max_user_connections = 1280 #max_connect_errors = 1280 #key_buffer_size = 8M #以MyISAM为主的服务器,要调大此值 #max_allowed_packet = 8M #table_cache = 4096 #table_open_cache = 4096 #table_definition_cache = 4096 #sort_buffer_size = 128K #read_buffer_size = 128K #read_rnd_buffer_size = 128K #join_buffer_size = 128K #tmp_table_size = 8M #max_heap_table_size = 8M #query_cache_type = 0 #query_cache_size = 0 #bulk_insert_buffer_size = 8M #thread_cache_size = 64 thread_concurrency = 4 #CPU核数*2 #thread_stack = 64K innodb_data_home_dir = /data/mysql/data innodb_log_group_home_dir = /data/mysql/logs/ #innodb_data_file_path = ibdata1:1G:autoextend #innodb_buffer_pool_size = 1G #innodb_buffer_pool_instances = 4 #innodb_additional_mem_pool_size = 2M #innodb_log_file_size = 16M #innodb_log_buffer_size = 4M #innodb_log_files_in_group = 3 #innodb_flush_log_at_trx_commit = 2 #innodb_lock_wait_timeout = 10 #innodb_sync_spin_loops = 40 #innodb_max_dirty_pages_pct = 90 #innodb_support_xa = 1 #innodb_thread_concurrency = 0 #innodb_thread_sleep_delay = 500 #innodb_file_io_threads = 4 #innodb_concurrency_tickets = 1000 #log_bin_trust_function_creators = 1 #innodb_flush_method = O_DIRECT #innodb_file_per_table#是否采用单表单空间 #innodb_write_io_threads = 8 #innodb_read_io_threads = 8 #innodb_io_capacity = 1000 #innodb_file_format = Barracuda#不开启单表单空间,此选项无效 #innodb_purge_threads = 1 #innodb_purge_batch_size = 32 #innodb_old_blocks_pct = 75 #innodb_change_buffering = all #transaction_isolation = READ-COMMITTED [mysqldump] quick max_allowed_packet = 4M [mysql] no-auto-rehash [myisamchk] #key_buffer_size = 4M #sort_buffer_size = 8M #read_buffer = 128k #write_buffer = 128k [mysqlhotcopy] interactive-timeout [mysqld_safe] #open-files-limit = 10240
配置
chown -R mysql.mysql /usr/local/mysql # 修改安装目录的权限
chmod +x /etc/rc.d/init.d/mysqld # 为启动脚本赋予可执行权限
/etc/init.d/mysqld start # 启动数据库服务
确定所有配置中的目录都是存在的并且权限为mysql:mysql,否则连最基本的错误日志都无法打开(如果错误日志本身的目录就错了)
配置mysql的root账号
/usr/local/mysql/bin/mysqladmin -u root -h localhost.localdomain password ‘151warden’ # 为了保险起见，使用命令全路径,注意，其中[client]中需要使用utf8(而不是utf-8)字符集，要么就不要指定.
初始化MySQL
/usr/local/mysql/scripts/mysql_install_db –user=mysql –datadir=/data/mysql/data/ –basedir=/usr/local/mysql
设置开机启动
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig –add mysqld
service mysqld start
chkconfig –level 35 mysqld on
配置主从同步账户
GRANT REPLICATION SLAVE ON . to ‘repl‘@’192.168.209.%’ IDENTIFIED BY ‘titps4gg’;
FLUSH PRIVILEGES;
在主库查看状态并且保存
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000013 | 843 | | | +------------------+----------+--------------+------------------+
复制主库的初始化数据到从库
(1) 执行mysqladmin -uroot -p shutdown关闭MySQL(master)服务器
(2) 日志文件应该单独指定文件夹，以免镜像时的复杂操作弄混
(3) 创建数据库的初始副本
cd /var/lib/mysql/ # 默认路径是这个，否则应该使用 /data/mysql/data/
tar vzcf snapshot.tgz . # 将整个文件夹打包成为snapshot.tgz
(4) 在从库所在服务器上拷贝出原有数据库初始化部分
scp root@192.168.209.129:/tmp/snapshot.tgz /tmp/
cd /data/
mv mysql/ mysql-bak/ # 将安装后的mysql文件先备份起来
mkdir mysql/data/ # 新建mysql文件夹,因为/etc/my.cnf中应该还是这么配置的
cd mysql/data/
tar xzfv /tmp/snapshot.tgz # 解压快照文件到当前目录
cd ..
chown mysql:mysql -R mysql/ # 将mysql文件夹的属主换为mysql用户
chcon -R -t mysqld_db_t /data/mysql/data/ # 将模式切换位 mysqld_db_t
在slave上配置master
CHANGE MASTER TO MASTER_HOST='192.168.209.129', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='titps4gg', MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=614;
关闭主服务器上的防火墙,最稳妥的是永久关闭，或者至少打开3306端口
chkconfig iptables on; # 重启后生效，打开 chkconfig iptables off; # 重启后生效，关闭 service iptables start; # 即时生效，重启后生效. service iptables stop; # 即时生效，重启后失效. vim /etc/sysconfig/iptables # 添加 -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT # 配置端口号开放 -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
启动slave:
mysql> start salve;
mysql> show slave status \G; ========================================== Slave_IO_Running: Yes # 确保为YES Slave_SQL_Running: Yes # 确保为YES ==========================================
常见错误解决方案
- 当复制出错时，尝试重启slave服务
mysql> stop slave;
mysql> start slave; - 自增字段的算法设置 vim /etc/my.cnf
auto_increment_offset = N (可以master1=1,master2=2..mastern=N)
auto_increment_increment = K (取值为master的数目，或者甚至可以预留出一部分来，便于后续使用,此时K>N,意味着每次自增的步长为K)
版权声明
本文标题:31-mysql双主多从配置
文章作者:盛领
发布时间:2015年08月31日 - 01:25:03
原始链接:http://blog.xiaoyuyu.net/post/457bf861.html
许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。
如您有任何商业合作或者授权方面的协商，请给我留言：sunsetxiao@126.com
