MySQL DBA 筆記

    更多文章:

    Percona Server

    Percona Server 是 MySQL 的一個分支版本,使用同樣的 MySQL 原始程式,不過整個資料庫系統都作了額外的功能強化與性能優化工作,除了在效能與性能有明顯改善以外,相關的 HA/Cluster 配套方案也都一應具全。

    Percona Server 還免費提供許多只有 MySQL 付費企業版才有的 features,所以更適合讓企業用在營運的重要系統上。

    企業若想使用 MySQL 這類的資料庫,但對於現有的 MySQL CE 版,無論在性能與功能上都不甚滿意時,不妨考慮 Percona Server,不僅是 OpenSource 可免費使用,有許多 feature 在營運系統上都很有用。

     

    資料庫備份/匯入

    // 匯出/備份資料庫的 schema

    # mysqldump --no-data --tables -uroot -p [db_name] > mydb-schema.sql

    Notes: 其他參數用法

    * --ignore-table=[db_name].[table_name] 忽略的資料表名稱,若為多個資料表,此參數需重複輸入。
    * 以上指令不會對 Trigger 及 procedure 做匯出。

    // 匯出/備份完整資料庫

    $> mysqldump --add-drop-table -uroot -p [db_name] > mydb-dump.sql

    Notes:

    若資料庫有包含 procedure,必須再加上參數 --routines。

    // 匯出指定 Table

    $> mysqldump --add-drop-table -uroot -p [db_name] Table-1 Table-2 > mydb-tables.sql
    

    // 匯出 Table 部分紀錄

    $> mysqldump --databases asteriskcdrdb --tables cdr --where="calldate > '2015-07-05' order by calldate desc" -uyouruser -p'yourpass'  > cdr.sql
    

    // 備份資料庫 - 完整 (for InnoDB)

    $> mysqldump --single-transaction --add-drop-table -uroot -p [db_name] > my_innodb.sql 
    

    TIPs:

    --single-transaction 避免線上資料交易影響備份工作

    參考教學:
    http://dev.mysql.com/doc/refman/5.0/...db-backup.html
    http://www.neo.com.tw/archives/1122
    http://www.softwareprojects.com/reso...base-1684.html

    // 備份交易資料 - 增量 (for InnoDB)
    NOTE: 資料庫啟動時,必須啟用 Binary Log (--bin-log) 功能,才能做增量備份。

    $> mysqldump --single-transaction --flush-logs --master-data=2 \
             --all-databases > backup_sunday_1_PM.sql 
    

    回復資料庫

    先回覆完整備份
    $> mysql < backup_sunday_1_PM.sql
    
    再回復 binary logs
    $>mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql
    

    TIPs:

    參考:
    https://dev.mysql.com/doc/refman/5.0...up-policy.html

    MySQL交易紀錄稱為 binary log。

     

    // 匯入資料庫

    方法一:
    # mysql -u<db-user> -p <db-name> < mydb-dump.sql
    
    方法二:
    # mysql -u<db-user> -p
    mysql> SET GLOBAL connect_timeout=60;
    mysql> use <db-name>;
    mysql> \. /path/to/mydb-dump.sql
    mysql> quit
     
    
    Bash Samples
    #!/bin/sh
    ...
    #Update Database
    RESULT=`/usr/bin/mysql -uroot -p$MYSQLROOTPASSWOOD <<SQL
    
    use asteriskcdrdb
    
    CREATE TABLE cdr_new (
    acctid integer NOT NULL AUTO_INCREMENT PRIMARY KEY,
    src varchar(80) NOT NULL,
    dst varchar(80) NOT NULL,
    ...
    ...
    uniqueid varchar(32) NOT NULL,
    userfield varchar(80) NOT NULL
    );
    
    RENAME TABLE cdr TO cdr_backup;
    
    RENAME TABLE cdr_new TO cdr;
    ALTER TABLE cdr ADD INDEX ( calldate );
    ALTER TABLE cdr ADD INDEX ( dst );
    ALTER TABLE cdr ADD INDEX ( accountcode );
    
    quit
    SQL`
    ...
    
    設定 MySQL 可接受外部主機的存取

    編輯 /etc/mysql/my.cnf
    註解這行 bind-address

    #bind-address            = 127.0.0.1
    

    DB用戶的權限必須允許外部主機存取,請參閱帳號管理

    MySQL Tuning and Optimization

    優化資料庫參考連結:

     

    簡易優化法:

    locate my-large.cnf
    locate my-huge.cnf 
    
    mysqladmin variables -uroot -p
    mysq> SHOW VARIABLES;
    mysql> SHOW STATUS;
    
    MySQL 系統參數

    // 查詢語法

    mysql> show variables like '%keyword%';
    使用 % 是模糊查詢。
    
    shell> mysqladmin variables -uroot -p
    

    // MySQL 版本

    mysql> show variables like 'version';

    // 系統狀態

    mysql> /s
    
    mysql  Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (i686) using readline 5.1
    
    
    Connection id:          29
    Current database:
    Current user:           [email protected]
    SSL:                    Not in use
    Current pager:          stdout
    Using outfile:          ''
    Using delimiter:        ;
    Server version:         5.0.77 Source distribution
    Protocol version:       10
    Connection:             127.0.0.1 via TCP/IP
    Server characterset:    latin1
    Db     characterset:    latin1
    Client characterset:    latin1
    Conn.  characterset:    latin1
    TCP port:               3306
    Uptime:                 4261 days 14 hours 34 min 50 sec
    
    DB效能監視

    - 圖表

    - 文字

    // 顯示目前的連線
    #mysqladmin -u root -p processlist 
    
    變更 root 密碼

    shell> mysqladmin -u root -p password 'newpass'
    需輸入舊密碼

    新增帳號

    方法一)

    #>mysql -u root -p mysql
    mysql> CREATE USER 'db_user'@'from_host' IDENTIFIED BY 'db_pass';
    mysql> GRANT ALL PRIVILEGES ON db_name.* TO 'db_user'@'from_host' WITH GRANT OPTION;

    方法二)

    #>mysql -u roop -p mysql
    mysql> INSERT INTO user
        ->     VALUES('from_host','db_user',PASSWORD('db_pass'),
        ->     'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
    mysql> FLUSH PRIVILEGES;

    TIP:

    from_host 連線用戶端的 IP 或 hostname,或者如下
    - localhost 限制僅local 存取
    - % 不限制來源

    查詢帳號

    // 列出所有帳號

    mysql>select host,user from mysql.user;
    

    // 列出帳號 user1 的權限

    mysql> SHOW GRANTS FOR [email protected];
    

    // 列出目前帳號的權限

    mysql> SHOW GTANTS;
    
    移除帳號
    mysql> DROP USER 'user1'@'localhost';
    
    變更一般用戶密碼

    方法一)

    #>mysql -u root -p
    mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('newpassword');
    mysql> flush privileges;

    方法二) 

    #>mysql -u root -p
    mysql> use mysql;
    mysql> UPDATE user SET Password=PASSWORD("密碼") WHERE User='目標使用者' AND Host = 'localhost';
    mysql> flush privileges;
    調整 SQL 最佳輸出顯示
    mysql> pager less -n -i -S;
    mysql> show table status;
    +---------------------+--------+---------+------------+------+----------------+-------------+-------------------+--
    | Name                | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length   | I
    +---------------------+--------+---------+------------+------+----------------+-------------+-------------------+--
    | Backup              | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |   281474976710655 |
    | admin               | MyISAM |      10 | Dynamic    |    2 |             32 |          64 |   281474976710655 |
    | ampusers            | MyISAM |      10 | Dynamic    |    1 |             44 |          44 |   281474976710655 |
    | announcement        | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |   281474976710655 |
    | callback            | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |   281474976710655 |
    | cidlookup           | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |   281474976710655 |
    | cidlookup_incoming  | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |   281474976710655 |
    | cronmanager         | MyISAM |      10 | Dynamic    |    1 |            100 |         100 |   281474976710655 |
    | custom_destinations | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |   281474976710655 |
    | custom_extensions   | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |   281474976710655 |
    | devices             | MyISAM |      10 | Dynamic    |    2 |             38 |          76 |   281474976710655 |
    | disa                | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |   281474976710655 |
    | extensions          | MyISAM |      10 | Dynamic    |    3 |             65 |         196 |   281474976710655 |
    | featurecodes        | MyISAM |      10 | Dynamic    |   39 |             54 |        2136 |   281474976710655 |
    | findmefollow        | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |   281474976710655 |
    

    加上 /G 以直列方式顯示結果
    NOTE: 不適用 rows 太多的查詢

    mysql> show table status like 'data_source_stats_hourly_cache' \G
    *************************** 1. row ***************************
               Name: data_source_stats_hourly_cache
             Engine: MEMORY
            Version: 10
         Row_format: Fixed
               Rows: 124417
     Avg_row_length: 73
        Data_length: 10244096
    Max_data_length: 9278300
       Index_length: 6654260
          Data_free: 191479
     Auto_increment: NULL
        Create_time: 2014-12-24 20:34:07
        Update_time: NULL
         Check_time: NULL
          Collation: utf8_general_ci
           Checksum: NULL
     Create_options:
            Comment:
    1 row in set (0.01 sec)
    
    關於 Store Engine (InnoDB/MyISAM)

    // 檢查 Table 的 storage engine type

    mysql> use <db_name>;
    mysql> pager less -n -i -S;
    mysql> show table status;
    mysql> show table status like "<table_name>";
    mysql> show table status from <db_name>;

    在 Linux console 可以改用

    顯示所有 Tables
    mysqlshow -uroot -p'password' --status <db_name>
    
    顯示指定的 Table
    mysqlshow -uroot -p'password' --status <db_name> <table_name> 
    

    // 檢查預設的 Store Engine Type

    mysql> show engines;
    mysql> show variables; 
    

    // 變更 Table 的 Store Engine Type

    mysql> ALTER TABLE <table_name> ENGINE=InnoDB;
    
    View 操作

    // 顯示資料庫的 VIEW

    mysql> show full tables in <table_name> where table_type like 'VIEW';
    
    Procedure 操作

    // 查詢 procedure

    mysql> show procedure status;
    
    Trigger 操作

    // 查詢 trigger

    mysql> show triggers like 'name%';
    mysql> show triggers;
    

    // 新增 trigger 的 script

    DELIMITER ;;
    SET SESSION SQL_MODE="ALLOW_INVALID_DATES" ;;
    CREATE DEFINER=`root`@`localhost` TRIGGER `cdr_insert` AFTER INSERT
     ON `cdr` FOR EACH ROW BEGIN
        DECLARE new_dst varchar(80);
        IF NEW.billsec > 0 THEN
          CALL fix_number(NEW.dst,@fixed_dst);
          CALL get_rate(@fixed_dst,NEW.dstchannel,NEW.billsec,@myname,@final_cost);
          INSERT INTO cdrcost (uniqueid,destination,cost) VALUES (NEW.uniqueid,@myname,@final_cost);
          INSERT INTO normalization (destination,fixed,dstchannel) VALUES (NEW.dst,@fixed_dst,NEW.dstchannel);
        END IF;
      END ;;
    

    Tips:

    若是非 root 用戶要新增 trigger,必須要有 super 權限,而且必須是 *.* 指令為:
    mysql> grant super on *.* to '<user>'@'localhost'

    ※這是個不安全的使用方法,不建議使用。

    // 移除 trigger

    mysql> DROP TRIGGER <schema.name>.<trigger.name>
    

    ※ schema.name 就是 database name。

    Primary key 與 Unique Key

    新增 Primary Key

    mysql> ALTER TABLE ans
    mysql>  ADD PRIMARY KEY (sn);
    

    移除 Primary Key

    mysql> ALTER TABLE ans DROP PRIMARY KEY;
    

    移除 Unique Key

    mysql> show create table ans;
    
    ----------------------------------+
    | ans   | CREATE TABLE `ans` (
      `SN` int(11) NOT NULL,   
      `AN` varchar(64) NOT NULL,
      `MonthsFromStart` int(11) NOT NULL,
      UNIQUE KEY `AN` (`AN`),  
      KEY `ClusteredIndex-20160930-104525` (`SN`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
    +-------+-------------------------------------
    
    mysql> ALTER table ans drop key AN;
    
    資料表(Table)操作

    // 檢查所有資料表 Table

    mysql> use <db_name>;
    mysql> show tables;
    mysql> show tables from <db_name>;

    顯示資料表的筆數、類型、建立時間等等資訊

    mysql> show table status;
    mysql> show table status like 'table-name'; 
    

    // 顯示既有資料表的 Create 的 SQL statement

    mysql> use <db_name>;
    mysql> show create table <table_name>;

    // 顯示資料表結構

    mysql> describe <table_name>;
    mysql> show columns from <table_name>;

    // 變更資料表名稱

    mysql> RENAME TABLE old_name TO new_name;
    

    // 新增一筆資料

    mysql> INSERT INTO <table_name> (col1,col2) VALUES(val1,val2);

    // 刪除一筆資料

    mysql> DELETE FROM <table_name> WHERE <column_name>='value';

    // 刪除所有資料

    mysql> DELETE FROM <table_name>;

    // 更新一筆資料
    //只更新 id=3這筆資料

    mysql> update <table> set <field>=replace(<field>,'<str_search>','<str_replace>') where id=3;

    // 新增一個欄位

    mysql> ALTER TABLE <table_name> ADD <column_name> VARCHAR(32);
    ※遇到有 dash 的 column name,需要加特殊符號 `,如以下指令
    mysql> ALTER TABLE <table_name> ADD `column-name` VARCHAR(32);

    // 增加欄位的字串長度

    mysql> ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(16) NOT NULL;

    // 資料表的 INDEX
    // 顯示 INDEX

    mysql> SHOW INDEX FROM <db_name>.<table_name>;

    // 新增 INDEX

    mysql> ALTER TABLE <table_name> ADD INDEX (column1);

    // 新增 UNIQUE INDEX

    mysql> ALTER TABLE <table_name> ADD UNIQUE (column2);

    // 修復損壞的資料表
    當操作資料表時,出現錯誤訊息: Table 'cc_system_log' is marked as crashed and should be repaired
    執行修復指令:

    mysql> repair TABLE <tab_name>;

    // 清除某個日期以前的資料
    檢視最後的 30 筆

    mysql>SELECT calldate FROM cdr WHERE calldate < '2011/1/1' ORDER BY calldate DESC LIMIT 30;
    mysql>SELECT count(*) FROM cdr WHERE calldate < '2011/1/1';
    mysql>DELETE FROM cdr WHERE calldate < '2011/1/1';
    資料庫(Database)操作

    //檢查資料庫名稱
    mysql> show databases;

    //連結資料庫
    mysql> use [db_name];

    //顯示資料庫的所有參數
    mysql> show variables;

    //新增資料庫
    mysql> CREATE DATABASE [db_name];

    //新增權限
    mysql> GRANT ALL PRIVILEGES ON [db_name].* TO [db_user]@localhost;
    mysql> GRANT ALL PRIVILEGES ON [db_name].* TO [db_user]@localhost WITH GRANT OPTION;

    //移除權限
    mysql> REVOKE ALL PRIVILEGES,GRANT OPTION ON [db_name].* FROM [db_user]@localhost;

    //刪除資料庫
    mysql> DROP DATABASE [db_name];

    在指令模式執行SQL statement

    $>mysql -u db_user -ppassword -e "show tables" database
    -p 與密碼之間不要有空格

    指令求助

    mysql> \h
    mysql> help
    mysql> help <command>;

    標籤 (Edit tags)
    • No tags

    文件 1

    文件大小日期附件上傳者 
     phpmyadmin-killer.php
    phpmyadmin killer
    169.96 KB12:19, 11 Feb 2010alang動作
    您必須 登入 才能發佈評論。
    Powered by MindTouch Core