mysql服务器主从配置简单说明

yum 卸载selinux 
yum remove selinux* -y

所涉及到软件版本
mysql-5.1.40.tar.gz

将上述软件包都放到/software/mysql下
----------------------------------------------------------------
一 解压、安装mysql
tar zxvf mysql-5.1.40.tar.gz
cd mysql-5.1.40
./configure --prefix=/usr/local/mysql --with-extra-charsets=all  --with-plugins=all
如果提示error: No curses/termcap library found
yum以下内容：
yum list|grep ncurses
yum -y install ncurses-devel
yum install ncurses-devel

make
make install

添加mysql用户和组
groupadd mysql
useradd -g mysql mysql

拷贝mysql配置文件my.cnf
cp support-files/my-medium.cnf /etc/my.cnf

初始化数据库
/usr/local/mysql/bin/mysql_install_db --user=mysql

以下步骤目的是把数据文件放到/data/(早期数据磁盘是单独挂载到此目录下的)目录下，方便迁移。可以选作
********************
mkdir /data/mysqldata
cp -au /usr/local/mysql/var/* /data/mysqldata
rm -rf /usr/local/mysql/var/
cd /usr/local/mysql/
ln -s /data/mysqldata var
chown -R mysql /data/mysqldata/
chgrp -R mysql /data/mysqldata/
*********************

添加环境变量
vi /etc/profile
添加
PATH=$PATH:/usr/local/mysql/bin/
source /etc/profile
关闭启动
mysqladmin -uroot -p shutdown
/usr/local/mysql/bin/mysqld_safe --user=mysql &
**安装最好采用centos4.8环境和rpm包安装，和公网保持一致，避免没必要的麻烦，如果想更换版本要进行测试**

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON bankaccount.* TO custom@localhost IDENTIFIED BY 'stupid';
GRANT all  privileges ON *.* TO root@'172.16.4.44' IDENTIFIED BY '123456';  
调整
mysql>use mysql
删除用于本机匿名连接的空密码帐号
mysql>delete from user where user="";
更改root密码
mysql>update user set Password=password('newpassword') where User='root';
mysql>flush privileges;
授权新用户
mysql>grant all privileges on DB.* to user@'172.22.5.%' identified by '123456';
移除用户
revoke all on *.* from dba@localhost;



二 主从配置
保证主库和从库的server-id不能一样
1) 主库
mysql > GRANT REPLICATION SLAVE ON *.*  TO repl@'172.22.5.%' IDENTIFIED BY '123456';
mysql > FLUSH TABLES WITH READ LOCK
dump一份数据库的sql文件，完成后。
mysql >show master status;
+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000001 | 106 |              |                  |
+------------------+-----------+--------------+------------------+
记录一下status文件
mysql> UNLOCK TABLES；


2) 从库
把sql文件拷贝到从库上，并source
mysql>CHANGE MASTER TO MASTER_HOST='172.22.5.22', MASTER_USER='repl', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106;
mysql>start slave;
mysql>show slave status;
----------------------------------------------------------------
七 涉及其他关联
1.内核参数调优
2.iptables防火墙策略(开放80等端口)
3.使用iptables不跟踪连接，解决连接跟踪问题(链接跟踪表满了，导致不能创建新的连接)
3.利用iptables-connlimit模块控制并发连接数(防止使用工具刷web页面等)
4.lvs后端realserver配置