mysql 入门

一、 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