mysql 安装

Mysql安 装

• 版本:5.6.20

• 编译参数:

cmake -DCMAKE_INSTALL_PREFIX=/xxx/mysql -DMYSQL_UNIX_ADDR=/xxx/mysql/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DMYSQL_DATADIR=/xxx/mysql/data -DMYSQL_USER=xxx

make

make install

单机初始化

1. 根据情况修改my.cnf.

2. cd $INSTALLDIR; ./scripts/mysql_install_db --user=eadop

   --datadir=/disk2/eadop/mysql/data/

   --defaults-file=/disk2/eadop/mysql/my.cnf

3. start.sh,stop.sh编写：

start:

./bin/mysqld_safe --defaults-file=/xxx/mysql/my.cnf --user=xxx --basedir=/xxx/mysql/ --datadir=/xxx/mysql/data/ --pid-file=/xxx/mysql/mysqld.pid &>/dev/null &
#./bin/mysqld_safe --user=xxx --basedir=/xxx/mysql/ --datadir=/xxx/mysql/data/ --pid-file=/xxx/mysql/mysqld.pid &>/dev/null &

stop:

./bin/mysqladmin -uroot -p shutdown

5.修改my.cnf中路径相关,根据主从修改my.cnf如下段

主从配置文件区别

'''master1:'''

socket = /disk2/mysql/mysql.sock

server-id = 1

auto_increment_offset = 1

#read_only

'''master2:'''

socket = /disk2/mysql/mysql.sock

server-id = 2

auto_increment_offset = 2

#read_only

'''slave:'''

socket = /disk2/mysql/mysql.sock

server-id = 3

#auto_increment_offset = 1

read_only

初始化步骤

1. ./start.sh启动mysql,ps ux查看进程,data/host.err文件查看是否有“Error”存在

2. ./bin/mysqladmin -uroot password 'new-password'****

3. ./bin/mysqladmin -uroot -h xxx password 'new-password'

4. ./bin/mysql_secure_installation

5. 注意本机客户端连接这个数据库的时候,需要用$INSTALLDIR/bin/mysq

   l 这个客户端连接上去,直接用系统自带的“mysql -uroot -p”会报错

6. root用户是默认不能其他机器远程登录的

双 master+slave配 置

更详细的可以参考这个 http://mysql-mmm.org/mmm2:guide

在两台机器上执行,增加监控和复制用户的权限

GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'10.168.%' IDENTIFIED BY 'monitor_password';

GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'10.168.%' IDENTIFIED BY 'agent_password';

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'10.168.%' IDENTIFIED BY 'replication_password';

FLUSH PRIVILEGES;

使两个库保持数据同步一致状态(新安装的忽略此步)。方法多样,具体步骤不 写了。

在 master1/master2 上面执行,并记录 master 的位置(重要)

(db1)mysql>FLUSH TABLES WITH READ LOCK;

(db1)mysql>SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000002 | 374 | |

| +------------------+----------+--------------+----------- -------+

 

 

1 row in set (0.00 sec)

在这步执行“FLUSH TABLES WITH READ LOCK”后,不要退出 mysql shell, 否则 read lock 会失效;

在几个 master/slave 机器上设置 change master。 两个 master 互相指定为 master,slave 指向其中一个 master

(db2) mysql> CHANGE MASTER TO master_host='101.101.0.11', master_port=3306, master_user='replication', master_password='replication_password', master_log_file='<file>', master_log_pos=<position>;

(db3) mysql> CHANGE MASTER TO master_host='192.168.0.11', master_port=3306, master_user='replication', master_password='replication_password', master_log_file='<file>', master_log_pos=<position>;

(db4) mysql> CHANGE MASTER TO master_host='102.102.0.11', master_port=3306, master_user='replication', master_password='replication_password', master_log_file='<file>', master_log_pos=<position>;

(db2) mysql> START SLAVE;

(db3) mysql> START SLAVE;

最后用 show slave status 查看 slave 状态;查看 mysql/data/$host.err 文件是否 有报错。在两边库上执行 create 看是否可以正常同步。

MMM配 置 (root)

安装 perl 依赖
需要开 ftp.perl.org 和 mirrors.xxx.com 的 ftp,iptables 权限,开完记得关掉

perl -MCPAN -e shell install Proc::Daemon install Algorithm::Diff installTime::HiRes installLog::Log4perl install Class::Singleton install Net::ARP 

备份工具：

percona-xtrabackup-2.0.0-417.rhel6.x86_64.rpm

rpm -ivh percona-xtrabackup-2.0.0-417.rhel6.x86_64.rpm

备份：nnobackupex --defaults-file=/home/xxx/mysql/my.cnf --socket=/home/xxx/mysql/mysql.sock --user=root --password=xxx --ibbackup=/usr/bin/xtrabackup_55 --safe-slave-backup --slave-info /home/xxx/mysql_dump

恢复：

1. innobackupex --defaults-file=/home/xxx/mysql/my.cnf --user=root --password=xxx --apply-log /home/xxx/mysql_dump/xxxx-xx-xx  
2. innobackupex --defaults-file=/home/xxx/mysql/my.cnf --user=root --password=xxx  --copy-back /home/xxx/mysql_dump/xxxx-xx-xx