Mysql主从架构集群部署

Mysql主从架构集群部署

编译安装

• 安装依赖
  sudo aptitude install cmake
  sudo aptitude install libncurses5-dev
  

  如果编译过程中有其他依赖不正确，按照提示进行安装即可。

• 编译

• cd mysql-5.6.26
  cmake \
  -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
  -DMYSQL_DATADIR=<data-path> \
  -DSYSCONFDIR=/etc \
  -DWITH_MYISAM_STORAGE_ENGINE=1 \
  -DWITH_INNOBASE_STORAGE_ENGINE=1 \
  -DWITH_MEMORY_STORAGE_ENGINE=1 \
  -DWITH_READLINE=1 \
  -DMYSQL_UNIX_ADDR=/var/run/mysqld/mysqld.sock \
  -DMYSQL_TCP_PORT=3306 \
  -DENABLED_LOCAL_INFILE=1 \
  -DWITH_PARTITION_STORAGE_ENGINE=1 \
  -DEXTRA_CHARSETS=all \
  -DDEFAULT_CHARSET=utf8 \
  -DDEFAULT_COLLATION=utf8_general_ci
  
  make 
  
  sudo mkdir /usr/local/mysql
  
  sudo make install
  

• 拷贝文件

  sudo mkdir /usr/share/mysql
  sudo cp -r /usr/local/mysql/share/* /usr/share/mysql
  

• 修改.bashrc

  vim ~/.bahrc
  

  在末尾添加
  PATH=/usr/local/mysql/bin:$PATH
  export PATH
  保存退出，执行

  source ~/.bashrc
  

主从集群配置

Mysql配置

这一部分操作在每一个节点上都需要做

• 创建mysql用户

  sudo groupadd mysql
  sudo useradd -r -g mysql mysql
  

• 创建mysql数据、日志、进程ip等目录，并修改权限

  mkdir <data-path>
  mkdir <log-path>
  sudo chown mysql.mysql -R <data-path>
  sudo chown mysql.mysql -R <log-path>
  

• 将mysql做成service
• 
  sudo cp /usr/local/mysql/support-files/mysql.server /etc/init.d/

• 数据库初始化

  cd /usr/local/mysql
  sudo scripts/mysql_install_db --user=mysql --datadir=<data-path>
  

• 创建配置文件

• sudo mkdir /etc/mysql
  sudo vim /etc/mysql/my.cnf
  

  配置文件中需要设置一下内容

  [client]
  port            = 3306
  socket          = /var/run/mysqld/mysqld.sock
  default-character-set = utf8mb4
  
  [mysqld_safe]
  socket          = /var/run/mysqld/mysqld.sock
  nice            = 0
  
  [mysqld]
  user            = mysql
  pid-file        = <pid-path>
  socket          = /var/run/mysqld/mysqld.sock
  port            = 3306
  basedir         = /usr/local/mysql
  datadir         = <data-path>
  tmpdir          = /tmp
  lc_messages_dir = /usr/share/mysql
  lc_messages     = en_US
  skip-external-locking
  max_connections         = 100
  connect_timeout         = 5
  wait_timeout            = 600
  max_allowed_packet      = 16M
  thread_cache_size       = 128
  sort_buffer_size        = 4M
  bulk_insert_buffer_size = 16M
  tmp_table_size          = 32M
  max_heap_table_size     = 32M
  myisam_recover          = BACKUP
  key_buffer_size         = 128M
  table_open_cache        = 400
  myisam_sort_buffer_size = 512M
  concurrent_insert       = 2
  read_buffer_size        = 2M
  read_rnd_buffer_size    = 1M
  query_cache_limit               = 128K
  query_cache_size                = 64M       
  general_log_file        = <log-path>/mysql.log
  general_log             = 1
  log_warnings            = 2
  log_error = <log-path>/error.log
  slow_query_log_file     = <log-path>/mysql-slow.log
  long_query_time = 10
  server-id               = 1
  read_only               = 1
  expire_logs_days        = 10
  max_binlog_size         = 100M
  character-set-server=utf8mb4
  collation-server=utf8mb4_unicode_ci
  character-set-client-handshake = FALSE
  [mysqldump]
  quick
  quote-names
  max_allowed_packet      = 16M
  
  [isamchk]
  key_buffer              = 16M
  
  !includedir /etc/mysql/conf.d/
  

  以上各配置项的数值根据具体需求进行配置。其中各个节点的server-id需要唯一，做master的主节点的不要配置read-only = 1

• 第一次启动mysql，需要修改密码

  sudo service mysql.server start  --skip-grant-tables
  mysql -u root
  use mysql
  update user set password=password(‘<password>') where user = 'root';
  quit;
  

• 重启mysql，创建用户及授权

  sudo service mysql.server restart
  mysql -u root -p;
  
  grant replication slave on *.* to 'repl_slave'@'%' identified by '<password>';
  flush privileges;
  

  创建repl_slave账户只需要在主节点上进行，创建teamie_web的操作旨在副节点上做。

  在添加teamie_server节点，从节点等时，都应该在数据库里加上ip的登陆授权，否则无法连接

主从结构部署

主节点

• 登陆主节点，获取当前数据库的数据

  mysql -u root -p
  

  锁表，导出数据库数据（在向现有的一个集群里添加一个从节点时，这一步是必须的，否则数据和主节点不一致）

  flush tables with read tables;
  

  另外开一个终端, 执行

  mysqldump -u root -p --all-databases > master.sql
  

  记录log-bin信息

  show master status;
  

  解锁表

  unlock tables;
  

从节点

以下的操作在每个从节点上都要做

• 导入数据–将从主节点导出的数据master.sql拷贝到从节点上。然后启动并登陆数据库

  sudo service mysql.server start
  mysql -u root -p
  source master.sql;
  

• 配置主节点信息

  change master to master_host = <master host>, 
               master_port = <port>, 
               master_user = <repl_user>, 
               master_password = <repl_password>, 
               master_log_file = <log-bin-file>, 
               master_log_pos = <position>;
  

• 启动从节点服务

  start slave;
  

• 查看从节点状态

  show slave status;
  

  主要看Slave_IO_Running和Slave_SQL_Running, 如果都为Yes，说明配置成功了。

配置日志切割

sudo vim /etc/logrotate.d/mysql

添加内容

<log-path>/*.log {
   weekly
   rotate 10
   copytruncate
   delaycompress
   compress
   notifempty
   missingok
}