CentOS 6.4 系統下的MySQL的主從庫配置

標籤：

首先瞭解到一、 二
一、（MySQL下建立使用者並且賦予許可權）root使用者建立yong使用者的SQL語句
 CREATE USER ‘yong‘@‘localhost‘ IDENTIFIED BY ‘yong2022‘;
 GRANT USAGE ON * . * TO ‘yong‘@‘localhost‘ IDENTIFIED BY ‘yong2022‘ WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
 
 FLUSH PRIVILEGES ;

二、(資料庫登入)
1、後台啟動mysql服務
/data/mysql/bin/mysqld_safe&
 【
     2、新安裝資料庫，設定mysql使用者名稱密碼
     /data/mysql/bin/mysqladmin -u root password ‘yong2022‘ 
 】  
3、使用者登入資料庫
/data/mysql/bin/mysql -u root -pyong2022

三、MySQL主從庫配置
 
目的： 將192.168.73.31（主機）的root使用者下的math和mathlog這兩個database 備份到 192.168.73.32（從機）的root使用者下，其他database不要求備份

1、在主機中建立使用者repl, 一般不給該使用者賦予訪問root的許可權
CREATE USER ‘repl‘@‘192.168.73.31‘ IDENTIFIED BY ‘yong2022‘;
2、改變主機的mysql設定檔：/data/mysql/etc/my.cnf【其中和主從相關語句已用 "# ****" 標註】

   [client]
   port            = 3306
   socket          = /tmp/mysql.sock

   [mysqld]
   datadir         = /data/mysql/var
   port            = 3306
   socket          = /tmp/mysql.sock
   skip_external_locking
   key_buffer_size = 384M
   max_allowed_packet = 1M
   table_open_cache = 512
   sort_buffer_size = 2M
   read_buffer_size = 2M
   read_rnd_buffer_size = 8M
   myisam_sort_buffer_size = 64M
   thread_cache_size = 8
   query_cache_size = 32M
   thread_concurrency = 4
   log_bin_trust_function_creators = 1/

   # handler socket
   loose_handlersocket_port = 9998
   loose_handlersocket_port_wr = 9999
   loose_handlersocket_threads = 16
   loose_handlersocket_threads_wr = 1
   open_files_limit = 65535

   log-bin=mysql-bin
   server-id       = 1                                                            # ****
   read-only = 0
   binlog-do-db =math                                                      # ****
   binlog-do-db =mathlog                                                 # ****
   binlog-ignore-db=information_schema                        # **** 不需要備份的database
   binlog-ignore-db=test                                                   # **** 不需要備份的database
       
   # innodb
   innodb_data_home_dir = /data/mysql/var
   innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
   innodb_log_group_home_dir = /data/mysql/var
   innodb_buffer_pool_size = 384M
   innodb_additional_mem_pool_size = 20M
   innodb_log_file_size = 100M
   innodb_log_buffer_size = 8M
   innodb_flush_log_at_trx_commit = 1
   innodb_lock_wait_timeout = 50

   [mysqldump]
   quick
   max_allowed_packet = 4M

   [mysql]
   no-auto-rehash

   [myisamchk]
   key_buffer_size = 256M
   sort_buffer_size = 256M
   read_buffer = 2M
   write_buffer = 2M

   [mysqlhotcopy]
   interactive-timeout

3、登入到主機mysql的root使用者，執行以下語句使從庫有訪問repl的許可權
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl‘@‘192.168.73.31‘ IDENTIFIED BY ‘yong2022‘;
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘repl‘@‘192.168.73.31‘IDENTIFIED BY ‘yong2022‘;
執行語句：
mysql> show master status;
此時，記錄下來File: xxxx 和  Position: yyyy 的值
4、改變從機的mysql設定檔：/data/mysql/etc/my.cnf 【其中和主從相關語句已用 "# ****" 標註】

   [client]
   port            = 3306
   socket          = /tmp/mysql.sock

   [mysqld]
   datadir         = /data/mysql/var
   port            = 3306
   socket          = /tmp/mysql.sock
   skip_external_locking
   key_buffer_size = 384M
   max_allowed_packet = 1M
   table_open_cache = 512
   sort_buffer_size = 2M
   read_buffer_size = 2M
   read_rnd_buffer_size = 8M
   myisam_sort_buffer_size = 64M
   thread_cache_size = 8
   query_cache_size = 32M
   thread_concurrency = 4
   log_bin_trust_function_creators = 1/

   # handler socket
   loose_handlersocket_port = 9998
   loose_handlersocket_port_wr = 9999
   loose_handlersocket_threads = 16
   loose_handlersocket_threads_wr = 1
   open_files_limit = 65535

   log-bin=mysql-bin
   server-id = 2                                                                  # ****區別於主機
   master-host=192.168.73.31                                       # ****
   master-user=repl                                                         # ****
   master-password=yong2022                                     # ****
   master-port=3306                                                       # ****
   master-connect-retry=60                                            # ****
   replicate-do-db=math                                                 # ****
   replicate-do-db=mathlog                                            # ****
   replicate-ignore-db=information_schema                # ****
   replicate-ignore-db=test                                             # ****

   # innodb
   innodb_data_home_dir = /data/mysql/var
   innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
   innodb_log_group_home_dir = /data/mysql/var
   innodb_buffer_pool_size = 384M
   innodb_additional_mem_pool_size = 20M
   innodb_log_file_size = 100M
   innodb_log_buffer_size = 8M
   innodb_flush_log_at_trx_commit = 1
   innodb_lock_wait_timeout = 50

   [mysqldump]
   quick
   max_allowed_packet = 16M

   [mysql]
   no-auto-rehash

   [myisamchk]
   key_buffer_size = 256M
   sort_buffer_size = 256M
   read_buffer = 2M
   write_buffer = 2M

   [mysqlhotcopy]
   interactive-timeout

5、登入從機的root使用者，執行以下語句
mysql> stop slave;
mysql> change master to master_host=‘192.168.73.31‘,
              master_user=‘repl‘,

              master_password=‘yong2022‘,
              master_log_file=‘xxxx‘,
              master_log_pos=yyyy;
mysql> start slave;
mysql> show slave status\G(這條SQl語句加";"會報錯)
6、當    
   Slave_IO_Running: Yes
   Slave_SQL_Running: Yes
 表示主從串連建立成功。
7、測試：可在主機的math下做操作，到從庫root使用者下驗證查看。
本文經過測試。
 參考博文：
 http://www.cnblogs.com/wanglikai91/archive/2012/04/17/2454503.html
 http://www.cnblogs.com/yangligogogo/articles/1939938.html
 http://wangwei007.blog.51cto.com/68019/965575

CentOS 6.4 系統下的MySQL的主從庫配置