mysql master slave

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配置