felo
作者felo2021-11-23 13:30
技术支持, IPS

MySQL on PowerLinux安装和复制配置

字数 7154阅读 967评论 0赞 0

1. 下载源码包

需要下载 Boost 版本,不然编译的时候会要求联网下载。

用到两个版本:

https://downloads.mysql.com/archives/get/p/23/file/mysql-boost-5.7.35.tar.gz

https://downloads.mysql.com/archives/get/p/23/file/mysql-boost-8.0.20.tar.gz

2. 编译

参考链接:

https://github.com/powerfans/MySQL_binaries/blob/main/scripts/build_on_rhel7_by_devtoolset/5.7.28/README.BUILD.TAR.txt

使用 devtoolset-7 进行编译,需要单独安装 devtoolset 包。

# lscpu
Architecture:  ppc64le
Byte Order:  Little Endian
Model name:  POWER9 (architected), altivec supported
# uname -a
Linux cn1 4.14.0-49.13.1.el7a.ppc64le #1 SMP Wed Sep 26 21:58:41 GMT 2018 ppc64le ppc64le ppc64le GNU/Linux

1 ) 5.7.35 版本二进制包:

make clean
source /opt/rh/devtoolset-7/enable \\
&& cmake3 .. \\
 -DBUILD_CONFIG=mysql_release \\  # 使用官方选项
 -DCMAKE_BUILD_TYPE=RelWithDebInfo \\
 -DCMAKE_C_COMPILER=`which gcc` \\
 -DCMAKE_C_FLAGS="-O3 -mcpu=native -mtune=native -mcmodel=large" \\
 -DCMAKE_CXX_COMPILER=`which g++` \\
 -DCMAKE_CXX_FLAGS="-O3 -mcpu=native -mtune=native -mcmodel=large" \\
 -DCMAKE_INSTALL_PREFIX=/opt/mysql/5.7.35 \\
 -DCMAKE_LINKER=`which gcc` \\
 -DCMAKE_AR=`which gcc-ar` \\
 -DCMAKE_NM=`which gcc-nm` \\
 -DCMAKE_RANLIB=`which gcc-ranlib` \\
 -DWITH_INNODB_MEMCACHED=ON \\
 -DWITH_BOOST=../boost/boost_1_59_0 \\
 -DWITH_NUMA=ON  \\
&& make -j16 \\
&& make install

2 ) 8.0.20 版本二进制包,需要使用 cmake3 :

make clean
source /opt/rh/devtoolset-7/enable \\
&& cmake3 .. \\
 -DBUILD_CONFIG=mysql_release \\
 -DCMAKE_BUILD_TYPE=RelWithDebInfo \\
 -DCMAKE_C_COMPILER=`which gcc` \\
 -DCMAKE_C_FLAGS="-O3 -mcpu=native -mtune=native -mcmodel=large" \\
 -DCMAKE_CXX_COMPILER=`which g++` \\
 -DCMAKE_CXX_FLAGS="-O3 -mcpu=native -mtune=native -mcmodel=large" \\
 -DCMAKE_INSTALL_PREFIX=/opt/mysql/8.0.20 \\
 -DCMAKE_LINKER=`which gcc` \\
 -DCMAKE_AR=`which gcc-ar` \\
 -DCMAKE_NM=`which gcc-nm` \\
 -DCMAKE_RANLIB=`which gcc-ranlib` \\
 -DWITH_INNODB_MEMCACHED=ON \\
 -DWITH_BOOST=../boost/boost_1_70_0 \\
 -DWITH_NUMA=ON  \\
&& make -j16 \\
&& make install

3. 启动测试

1 )编写一个 my.cnf

[mysqld]

basedir=/opt/mysql/5.7.35/

datadir=/opt/mysql/mysql/data/

log-error=/opt/mysql/mysql/mysqld.log

default_authentication_plugin=mysql_native_password

lower_case_table_names=1

key_buffer_size = 2048M

join_buffer_size = 256K

sort_buffer_size = 256K

open_files_limit = 65535

table_open_cache = 10000

table_open_cache_instances=32

max_connections=1250

back_log=1250

default_storage_engine=InnoDB

default_tmp_storage_engine=InnoDB

innodb_log_file_size=1024M

innodb_log_files_in_group = 9

innodb_open_files=8000

innodb_file_per_table = true

innodb_data_file_path = ibdata1:1000M:autoextend

innodb_buffer_pool_size = 8G

innodb_buffer_pool_instances = 32

innodb_log_buffer_size=2048M

server-id=1

long_query_time=0.10

performance_schema=OFF

2 )初始化

export PATH=/opt/mysql/5.7.35/bin:$PATH

mkdir –p /opt/mysql/mysql/data/

useradd mysql

chown mysql:mysql /opt/mysql/mysql/ -R

初始化数据库:

mysqld --defaults-file=/opt/mysql/my.cnf --user=mysql –initialize

启动:

mysqld --defaults-file=/opt/mysql/my.cnf --user=mysql &

完成后在 log-error 中可以看到初始密码

mysql_secure_installation

4. MySQL 复制配置

有三个机器,分别是:

CN3 : 192.168.122.141 , MySQL : 5.7.35  --master

CN1 : 192.168.122.190 , MySQL : 8.0.20  --slave

CN2 : 192.168.122.4 ,  MySQL : 8.0.20  --slave

1 )对空库配置

Master配置( cn3 ):

修改 my.cnf 文件

server-id=103

log_bin=mysql-bin

查看 master 日志状态:

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File  | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000002 |  398 |  |  |  |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

增加复制用户并赋权,不需在备库上执行,会自动复制到备库:

mysql> create user 'repl'@'%' identified by 'rootroot';

Query OK, 0 rows affected (0.05 sec)

mysql> grant replication slave, replication client on *.* to 'repl'@'%';

Query OK, 0 rows affected (0.05 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.05 sec)

Slave配置( cn2 ):

修改配置文件:


server-id=102  # cn1 修改为 101 ,唯一

log_bin=mysql-bin

relay_log = /opt/mysql/mysql/data/relay_log_bin

log_slave_updates =1

read_only = 1

修改复制选项:

mysql> change master to master_host='cn3',

 -> master_user='repl',

 -> master_password='rootroot',

 -> master_log_file='mysql-bin.000002',

 -> master_log_pos=0;

Query OK, 0 rows affected, 2 warnings (0.87 sec)

启动复制:

mysql> start slave;

Query OK, 0 rows affected (0.06 sec)

mysql> show slave status\\G
*************************** 1. row ***************************

  Slave_IO_State: Waiting for master to send event  # 已完成

 Master_Host: cn3

 Master_User: repl

 Master_Port: 3306

 Connect_Retry: 60

 Master_Log_File: mysql-bin.000002

  Read_Master_Log_Pos: 1011

 Relay_Log_File: relay_log_bin.000002

 Relay_Log_Pos: 1226

 Relay_Master_Log_File: mysql-bin.000002

 Slave_IO_Running: Yes

 Slave_SQL_Running: Yes

 …

 Exec_Master_Log_Pos: 1011  # 位置与 master 日志位置相同

 …

1 row in set (0.01 sec)

已经将 master 创建的 repl 复制过来

mysql> select user,host from mysql.user where user='repl';

+------+------+

| user | host |

+------+------+

| repl | %  |

+------+------+

1 row in set (0.02 sec)

2 ) master 已有数据

需要停止 master ,直接将 data 复制到 slave 上进行操作。

Master操作:

对 master 操作库增加数据( MySQL 必知必会中的脚本):

[root@cn3 mysql]# cat create.sql |mysql -uroot -prootroot test

[root@cn3 mysql]# mysql -uroot -prootroot test < populate.sql

关闭数据库

[root@cn3 mysql]# mysqladmin shutdown -uroot -prootroot

修改 my.cnf

server-id=103

log_bin=mysql-bin

启动,创建复制用户

[root@cn3 mysql]# mysqld --defaults-file=my.cnf --user=mysql &

mysql> create user 'repl'@'%' identified by 'rootroot';

mysql> grant replication slave, replication client on *.* to 'repl'@'%';

mysql> flush privileges;

mysql> show master status;

关闭数据库,并将 data 目录传输到备库

[root@cn3 mysql]# mysqladmin shutdown -uroot -prootroot

[root@cn3 mysql]# scp -r mysql/data/* cn2:/opt/mysql/mysql/data/

Slave操作( cn2 ):

修改 my.cnf

server-id=102

log_bin=mysql-bin

relay_log = /opt/mysql/mysql/data/relay_log_bin

log_slave_updates =1

read_only = 1

启动,注意 mysql data 文件的权限

# rm –rf mysql/data/auto.cnf  # UUID 不能重复

# mysqld --defaults-file=./my-8.cnf --user=mysql &

修改配置并启动,

先看下 master status 上显示的是 mysql-bin.000003 , 154

mysql> stop slave;

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> change master to master_host='cn3',

 -> master_user='repl',

 -> master_password='rootroot',

 -> master_log_file='mysql-bin.000003',

 -> master_log_pos=154;

Query OK, 0 rows affected, 2 warnings (0.67 sec)

mysql> start slave;

mysql> show slave status\\G

查看 Exec_Master_Log_Pos 是否与 master 的 Position 一致;

Master 上可以看到两个 slave :

mysql> show slave hosts;

+-----------+------+------+-----------+--------------------------------------+

| Server_id | Host | Port | Master_id | Slave_UUID  |

+-----------+------+------+-----------+--------------------------------------+

|  101 |  | 3306 |  103 | 8c826edb-4bd2-11ec-91df-5254003b85c3 |

|  102 |  | 3306 |  103 | 7cd617fa-4bc4-11ec-83c9-525400a70c1d |

+-----------+------+------+-----------+--------------------------------------+

2 rows in set (0.00 sec)

如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!

0

添加新评论0 条评论

Ctrl+Enter 发表

作者其他文章

相关文章

相关问题

相关资料

X社区推广