分类目录归档：MySQL安装配置

1、数据库实例目录
drwxrwxrwx 6 mysql mysql 4096 Jul 6 23:25 mysqldata
drwxrwxr-x 5 mysql mysql 4096 Jul 6 23:23 mysqldata1
drwxrwxr-x 5 mysql mysql 4096 Jul 6 23:27 mysqldata2

2、创建实例
/opt/mysql/product/5.5/scripts/mysql_install_db –basedir=/opt/mysql/product/5.5 –datadir=/opt/mysql/mysqldata –user=mysql
/opt/mysql/product/5.5/scripts/mysql_install_db –basedir=/opt/mysql/product/5.5 –datadir=/opt/mysql/mysqldata1 –user=mysql
/opt/mysql/product/5.5/scripts/mysql_install_db –basedir=/opt/mysql/product/5.5 –datadir=/opt/mysql/mysqldata2 –user=mysql

3、修改my.cnf文件
[mysqld_multi]
mysqld = /opt/mysql/product/5.5/bin/mysqld
mysqladmin = /opt/mysql/product/5.5/bin/mysqladmin
user = root
password = passw0rd

[mysqld3306]
port = 3306
socket = /var/run/mysqld/mysqld3306.sock
pid-file = /var/run/mysqld/mysqld3306.pid
basedir = /opt/mysql/product/5.5
datadir = /opt/mysql/mysqldata

[mysqld3307]
port = 3307
socket = /var/run/mysqld/mysqld3307.sock
pid-file = /var/run/mysqld/mysqld3307.pid
basedir = /opt/mysql/product/5.5
datadir = /opt/mysql/mysqldata1

[mysqld3307]
port = 3307
socket = /var/run/mysqld/mysqld3308.sock
pid-file = /var/run/mysqld/mysqld3308.pid
basedir = /opt/mysql/product/5.5
datadir = /opt/mysql/mysqldata2
注：其他无关参数省略，日志等文件路径也不能相同（未写明）

4、启动数据库
mysqld_multi –defaults-file=/etc/mysql/my.cnf start 3306
mysqld_multi –defaults-file=/etc/mysql/my.cnf start 3307
mysqld_multi –defaults-file=/etc/mysql/my.cnf start 3308

5、登录数据库修改root密码
mysql -S /var/run/mysqld/mysqld3306.sock
mysql -S /var/run/mysqld/mysqld3307.sock
mysql -S /var/run/mysqld/mysqld3308.sock

use mysql
UPDATE user SET password=PASSWORD(‘passw0rd’) WHERE user=’root’;
FLUSH PRIVILEGES;
说明：三个库上分别操作

6、关闭数据库
mysqld_multi –defaults-file=/etc/mysql/my.cnf stop 3306
mysqld_multi –defaults-file=/etc/mysql/my.cnf stop 3307
mysqld_multi –defaults-file=/etc/mysql/my.cnf stop 3308

7、全部启动/关闭
mysqld_multi –defaults-file=/etc/mysql/my.cnf start 3306-3308
mysqld_multi –defaults-file=/etc/mysql/my.cnf stop 3306-3308

1、备份主机数据
innobackupex –user=root –password=xifenfei –defaults-file=/etc/my.cnf –stream /tmp/mysql 2>/tmp/mysql.log | gzip> /tmp/data/mysql.tar.gz

2、查看/tmp/mysql.log，记录下当前日志位置
innobackupex: MySQL binlog position: filename ‘mysql-bin.000018′, position 107

3、备份文件恢复
scp /tmp/data/mysql.tar.gz root@192.168.1.2:/tmp/
mkdir /tmp/mysql
cd /tmp/mysql
tar izxvf /tmp/mysql.tar.gz
–恢复数据库
innobackupex –apply-log –user=root –password=xifenfei /tmp/mysql
–复制到my.cnf中指定地方
innobackupex –copy-back –user=root –password=xifenfei /tmp/mysql
chmod -R mysql.mysql /var/lib/mysql

4、修改my.cnf文件
主服务器：
server-id=1
innodb_flush_log_at_trx_commit=1
sync-binlog=1

从服务器：
server-id=2
relay-log=/var/lib/mysql/replicate
relay-log-index=/var/lib/mysql/replicate.index
read-only

7、添加复制用户（主数据库上）
GRANT REPLICATION SLAVE ON *.*
TO ‘repl’@’192.168.1.2′ IDENTIFIED BY ‘xifenfei’;

8、配置从数据库
CHANGE MASTER TO
MASTER_HOST=’192.168.1.4′,
MASTER_USER=’repl’,
MASTER_PASSWORD=’xifenfei’,
MASTER_LOG_FILE=’mysql-bin.000018′,
MASTER_LOG_POS=107;
start slave;

9、查看主从是否正常
SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.4
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000022
Read_Master_Log_Pos: 1185
Relay_Log_File: replicate.000007
Relay_Log_Pos: 588
Relay_Master_Log_File: mysql-bin.000022
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1185
Relay_Log_Space: 1627
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1

SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000022
Position: 1185
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

SHOW PROCESSLIST\G
–从服务器进程
*************************** 2. row ***************************
Id: 22
User: system user
Host:
db: NULL
Command: Connect
Time: 1136
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 23
User: system user
Host:
db: NULL
Command: Connect
Time: 1676370
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
–主服务器进程
*************************** 1. row ***************************
Id: 14
User: repl
Host: 192.168.1.2:34594
db: NULL
Command: Binlog Dump
Time: 1207
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL

10、管理从服务器
start slave;
reset slave;
stop slave;