一、 mysql目录结构:
bin COPYING data docs include INSTALL-BINARY lib man mysql-test README scripts share sql-bench support-files
1. bin目录
主要放mysql的一些二进制bin文件,比较常用的有:
mysqld_safe: mysqld_safe是一个安全启动bash脚本
mysqld mysqld服务
mysqladmin mysql管理:reload,shutdown,status,start-slave,stop-slave
mysqldump mysqldump
2. data目录
mysql默认存放数据的目录,但一般不会放在此目录
3.docs目录
mysql.info 手册
4.include目录
存放一些mysql的头文件
5. lib目录
mysql的库文件
6.mysql-test
This directory contains a test suite for the MySQL daemon. To run
the currently existing test cases, simply execute ./mysql-test-run in
this directory. It will fire up the newly built mysqld and test it.
7.scripts
mysql 安装脚本,如果一不小心把mysql的本地数据库删掉可以通过此脚本恢复,/mysql_install_db --user=yunpeng --basedir=/usr/local/mysql --datadir=/duitang/data/mysql
注意:mysql_install_db也有可能在bin目录下。
8.share
9. support-files
默认的配置文件:
my-small.cnf
my-medium.cnf
my-large.cnf
二、 mysql启动
比较安全的启动方式是通过mysqld_safe启动:
1. /usr/local/mysql/bin/mysqld_safe --user=yunpeng --basedir=/usr/local/mysql/ --datadir=/duitang/data/mysql/
2. /usr/local/mysql/bin/mysqld_safe --defaults-file=/duitang/dist/conf/mysql/my.cnf
比较好的方案是通过my.conf来启动。
三、 my.conf配置
basedir =/usr/local/mysql
datadir =/duitang/data/mysql
log-bin =mysql-bin
binlog_format =mixed
四、master,slave
操作步骤:
1.master
master需要为slave创建一个用户权限:
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.172.9' IDENTIFIED BY '123456';
REPLICATION SLAVE: 表示复制权限
ON *.*: 表示所有数据库
replication:表示replication用户
@'192.168.172.9': 表示开放的ip
IDENTIFIED BY '123456': 表示密码是123456
创建之后:
mysql> select * from user where User='replication' \G;
*************************** 1. row ***************************
Host: 192.168.172.1
User: replication
Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: Y
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
1 row in set (0.00 sec)
2. slave:
slave执行:
CHANGE MASTER TO MASTER_HOST='192.168.172.11', MASTER_USER='replication', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000000', MASTER_LOG_POS=00000000;
3. 可以检查一下bin_log过期时间
mysql> show variables like 'expire_logs_days%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 10 |
+------------------+-------+
1 row in set (0.00 sec)
这个默认是0,也就是logs不过期,现在是10天也有点长了,推荐3天
set global expire_logs_days=3;
五、 mysql停机
mysqladmin -u root shutdown
五、 问题记录:
启动出现如下错误日志
130709 15:12:15 [ERROR] Could not use /duitang/logs/mysql/zcwdb/mysql_slow.log for logging (error 2). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.
130709 15:12:15 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
先执行script
mysql_install_db --user=admin --basedir=/duitang/dist/sys/mysql --datadir=/duitang/data/mysql/zcwdb