第五周作业

10/19 07:26
阅读数 20

1.主节点配置:
vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
log_bin
server_id=8



 systemctl restart mysqld

 mysql
 show master logs;
 grant replication slave on *.* to repluser@'10.0.0.%' identified by 'song';

 从节点配置:
 vim /etc/my.cnf.d/mysql-server.cnf
 [mysqld]
 server_id=18

 systemctl restart mysqld

 mysql
 CHANGE MASTER TO MASTER_HOST='10.0.0.8',
 MASTER_USER='repluser',MASTER_PASSWORD='centos',MASTER_PORT=3306,
 MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=545;
 start slave;

2.cat /var/lib/mysql/relay-log.info
vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=18
read-only=OFF
log-bin=/data/mysql/logbin/mysql-bin




 set global read-only=off;
 stop slave;
 reset slave all;

 mysqldump -A --single-transaction --master-data=1 -F > backup.sql
 scp backup.sql 10.0.0.28

 vim backup.sql
 CHANGE MASTER TO
    MASTER_HOST='10.0.0.18',
        MASTER_PASSWORD='centos',
        MASTER_PORT=3306,
        MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=371;

mysql
stop slave;
reset slave all;
set sql_log_bin=off;
source backup.sql;
set sql_log_bin=on;
start slave;

3.yum -y install mha4mysql-manager-0.58-0.e17.centos.noarch.rpm
yum -y install mha4mysql-node-0.58-0.e17.centos.noarch.rpm

 yum -y install mha4mysql-node-0.58-0.e17.centos.noarch.rpm

 ssh-keygen
 ssh-copy-id 10.0.0.7
 rsync -av .ssh 10.0.0.8:/root/
 rsync -av .ssh 10.0.0.18:/root/
 rsync -av .ssh 10.0.0.28:/root/
 mkdir /etc/mastermha/
 vim /etc/mastermha/app1.cnf
 [server default]
 user=mhauser
 password=centos
 manager_workdir=/data/mastermha/app1/
 manager_log=/data/mastermha/app1/manager.log
 remote_workdir=/data/mastermha/app1/
 ssh_user=root
 repl_user=repluser
 repl_password=centos
 ping_interval=1
 master_ip_failover_script=/usr/local/bin/master_ip_failover
 report_script=/usr/local/bin/sendmail.sh
 check_repl_delay=0

 [server1]
 hostname=10.0.0.8
 candidate_master=1
 [server2]
 hostname=10.0.0.18
 candidate_master=1
 [server3]
 hostname=10.0.0.28

 mkdir /data/mysql/
 chown mysql.mysql /data/mysql/
 vim /etc/my.cnf
 [mysqld]
 server_id=1
 log-bin=/data/mysql/mysql-bin
 skip_name_resolve=1

 mysql
 show master logs;
 grant replication slave on *.* to repluser@'10.0.0.%' identified by 'centos';
 grant all on *.* to mhauser@'10.0.0.%' identified by 'centos';

 ifconfig eth0:1 10.0.0.100/24

 mkdir /data/mysql
 chown mysql.mysql /data/mysql/
 vim /etc/my.cnf
 [mysqld]
 server_id=2
 log-bin=/data/mysql/mysql-bin
 read-only
 relay_log_purge=0
 skip_name_resolve=1

 mysql
 CHANGE MASTER TO MASTER_HOST='MASTER_IP',MASTER_USER='repluser',
 MASTER_PASSWORD='centos',MASTER_LOG_FILE='mysql-bin.000001',
 MASTER_LOG_POS=245;
 START SLAVE;

 masterha_check_ssh --conf=/etc/mastermha/app1.cnf
 masterha_check_repl --conf=/etc/mastermha/app1.cnf
 masterha_check_status --conf=/etc/mastermha/app1.cnf

 nohup masterha_manager --conf=/etc/mastermha/app1.cnf &> /dev/null
 masterha_check_status --conf=/etc/mastermha/app1.cnf

4.vim /etc/yum/repos.d/pxc.repo
[percona]
name=percona_repo
baseurl = https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearch
enabled = 1
gpgcheck = 0




 scp /etc/yum.repos.d/pxc.repo 10.0.0.17:/etc/yum.repos.d
 scp /etc/yum.repos.d/pxc.repo 10.0.0.27:/etc/yum.repos.d

 yum -y install Percona-XtraDB-Cluster-57
 yum -y install Percona-XtraDB-Cluster-57
 yum -y install Percona-XtraDB-Cluster-57

 vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
 [mysqld]
 wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
 wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.17,10.0.0.27
 binlog_format=ROW
 default_storage_engine=InnoBD
 wsrep_slave_threads=8
 wsrep_log_conflicts
 innodb_autoinc_lock_mode=2
 wsrep_node_address=10.0.0.7
 wsrep_cluster_name=pxc-cluster
 wsrep_node_name=pxc-cluster-node-1
 pxc_strict_mode=ENFORCING
 wsrep_sst_method=xtrabackup-v2
 wsrep_sst_auth="sstuser:s3cretPass"

 grep -Ev "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
  [mysqld]
 wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
 wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.17,10.0.0.27
 binlog_format=ROW
 default_storage_engine=InnoBD
 wsrep_slave_threads=8
 wsrep_log_conflicts
 innodb_autoinc_lock_mode=2
 wsrep_node_address=10.0.0.17
 wsrep_cluster_name=pxc-cluster
 wsrep_node_name=pxc-cluster-node-2
 pxc_strict_mode=ENFORCING
 wsrep_sst_method=xtrabackup-v2
 wsrep_sst_auth="sstuser:s3cretPass"

 grep -Ev "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
  [mysqld]
 wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
 wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.17,10.0.0.27
 binlog_format=ROW
 default_storage_engine=InnoBD
 wsrep_slave_threads=8
 wsrep_log_conflicts
 innodb_autoinc_lock_mode=2
 wsrep_node_address=10.0.0.27
 wsrep_cluster_name=pxc-cluster
 wsrep_node_name=pxc-cluster-node-3
 pxc_strict_mode=ENFORCING
 wsrep_sst_method=xtrabackup-v2
 wsrep_sst_auth="sstuser:s3cretPass"

 systemctl start mysql@bootstrap.service
 grep "temporary password" /var/log/mysqld.log
 mysql -uroot -p'=tWFP0oRJl8t'
 alter user 'root'@'localhost' identified by 'centos';
 CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 's3cretPass';
 GRANT RELOAD,LOCK TABLES,PROCESS,REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
 SHOW VARIABLES LIKE 'wsrep%'\G
 SHOW STATUS LIKE 'wsrep%'\G
 show status like 'wsrep%';

 systemctl start mysql
 systemctl start mysql

 mysql -uroot -pcentos
 show variables like 'wsrep_node_name';
 show variables like 'wsrep_node_address';
 show variables like 'wsrep_on';
 show variables like 'wsrep_cluster_size';

5.vim /data/ansible/files/my.cnf
[mysqld]
socket=/tmp/mysql.sock
user=mysql
symbolic-links=0
datadir=/data/mysql
innodb_file_per_table=1
log-bin
pid-file=/data/mysql/mysqld.pid







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

 [mysqld_safe]
 log-error=/var/log/mysqld.log

 vim /data/ansible/files/secure_mysql.sh
 #!/bin/bash
 /usr/local/mysql/bin/mysql_secure_installation <<EOF

 y
 centos
 centos
 y
 y
 y
 y
 EOF

 vim /data/ansible/install_mysql.yml
 ---
 #install mysql-8.0.21-linux-glibc2.12-x86_64.tar.gz
 - hosts:dbsrvs
   remote_user:root
     gather_facts:no

     tasks:
       - name:install packages
         yum:name=libaio,perl-Data-Dumper,perl-Getopt-Long
         - name:create mysql group
           group:name=mysql gid=306
         - name:create mysql user
           user:name=mysql uid=306 group=mysql shell=/sbin/nologin system=yes create_home=no home=/data/mysql
         - name:copy tar to remote host and file mode
           unarchive:src=/data/ansible/files/mysql-8.0.21-linux-glibc2.12-x86_64.tar.gz dest=/usr/local/ owner=root group=root
         - name:create linkfile /usr/local/mysql
           file:src=/usr/local/mysql-8.0.21-linux-glibc2.12-x86_64 dest=/usr/local/mysql state=link
         - name:data dir
           shell:chdir=/usr/local/mysql/ ./scripts/mysql_install_db --datadir=/data/mysql --user=mysql
             tags:data
         - name:config my.cnf
           copy:src=/data/ansible/files/my.cnf dest=/etc/my.cnf
         - name:service script
           shell:/bin/cp /usr/local/mysql/support-files/mysql/server /erc/init.d/mysqld
         - name:enable service
           shell:/etc/init.d/mysqld start;chkconfig --add mysqld;chkconfig mysql in
             tags:service
         - name:PATH varable
           copy:content='PATH=/usr/local/mysql/bin:$PATH' dest=/etc/profile.d/mysql.sh
         - name:secure script
           script:/data/ansible/files/secure_mysql.sh
             tags:script
展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部