當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

amoeba mysql_从零开始：Mysql基于Amoeba的集群搭建

發布時間：2023/12/18 数据库 27 豆豆

生活随笔收集整理的這篇文章主要介紹了 amoeba mysql_从零开始：Mysql基于Amoeba的集群搭建小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

從零開始：Mysql基于Amoeba的集群搭建

準備環境

1、mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz

2、amoeba-mysql-binary-2.0.1-BETA.tar.gz

3、Centos7

下載安裝配置運行

1、mysql安裝

使用wget命令下載安裝

//創建目錄

mkdir /software/mysql

cd /software/mysql

wget https://dev.mysql.com/get/downloads/mysql-5.7/mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz

下載安裝完成后使用tar命令解壓

tar zxvf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz /software/mysql/mysql3307

我這里搭建的是一主兩從模式，所以需要三個mysql實例。

cp -rf /software/mysql/mysql3307 /software/mysql/mysql3308

cp -rf /software/mysql/mysql3307 /software/mysql/mysql3309

2、mysql配置

在每個實例下的根路徑創建一個my.cnf文件，一個data目錄。

對每個實例修改相應的端口和目錄。

[client]

no-beep

socket=/software/mysql/mysql3307/mysql.sock

# pipe

# socket=0.0

port=3307

[mysqld]

basedir=/software/mysql/mysql3307

datadir=/software/mysql/mysql3307/data

port=3307

pid-file=/software/mysql/mysql3307/mysqld.pid

#skip-grant-tables

skip-name-resolve

character-set-server=utf8

default-storage-engine=INNODB

explicit_defaults_for_timestamp = true

#----開啟主從配置----

log-bin=mysql-bin

server-id = 1

sync_binlog=1

# -----------------

max_connections=2000

query_cache_size=0

table_open_cache=2000

tmp_table_size=246M

thread_cache_size=300

#限定用于每個數據庫線程的棧大小。默認設置足以滿足大多數應用

thread_stack = 192k

key_buffer_size=512M

read_buffer_size=4M

read_rnd_buffer_size=32M

innodb_data_home_dir = /software/mysql/mysql3307/data

innodb_flush_log_at_trx_commit=0

innodb_log_buffer_size=16M

innodb_buffer_pool_size=256M

innodb_log_file_size=128M

innodb_thread_concurrency=128

innodb_autoextend_increment=1000

innodb_buffer_pool_instances=8

innodb_concurrency_tickets=5000

innodb_old_blocks_time=1000

innodb_open_files=300

innodb_stats_on_metadata=0

innodb_file_per_table=1

innodb_checksum_algorithm=0

back_log=80

flush_time=0

join_buffer_size=128M

max_allowed_packet=1024M

max_connect_errors=2000

open_files_limit=4161

query_cache_type=0

sort_buffer_size=32M

table_definition_cache=1400

binlog_row_event_max_size=8K

sync_master_info=10000

sync_relay_log=10000

sync_relay_log_info=10000

#批量插入數據緩存大小，可以有效提高插入效率，默認為8M

bulk_insert_buffer_size = 64M

interactive_timeout = 120

wait_timeout = 120

log-bin-trust-function-creators=1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# include all files from the config directory

創建用戶組

#創建用戶組

groupadd -r mysql

useradd -r -g mysql mysql

#設置用戶組

chown -R mysql:mysql /software/mysql/

chown -R mysql /software/mysql/

使用mysqld命令初始化mysql。

cd /software/mysql/mysql3307/bin

./mysqld --user=mysql --basedir=/software/mysql/mysql3307 --datadir=/software/mysql/mysql3307/data --initialize

./mysqld --user=mysql --defaults-file=/software/mysql/mysql3307/my.cnf

cd /software/mysql/mysql3308/bin

./mysqld --user=mysql --basedir=/software/mysql/mysql3308 --datadir=/software/mysql/mysql3308/data --initialize

./mysqld --user=mysql --defaults-file=/software/mysql/mysql3308/my.cnf

cd /software/mysql/mysql3309/bin

./mysqld --user=mysql --basedir=/software/mysql/mysql3309 --datadir=/software/mysql/mysql3309/data --initialize

./mysqld --user=mysql --defaults-file=/software/mysql/mysql3309/my.cnf

初始化完成時會輸出一個mysql的初始密碼(Tsiq_elgs7iQ)。

2019-11-24T01:06:22.321101Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2019-11-24T01:06:23.032518Z 0 [Warning] InnoDB: New log files created, LSN=45790

2019-11-24T01:06:23.146942Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2019-11-24T01:06:23.205353Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: a1c226a9-0e56-11ea-ac41-00163e0c5978.

2019-11-24T01:06:23.206882Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2019-11-24T01:06:23.207553Z 1 [Note] A temporary password is generated for root@localhost: Tsiq_elgs7iQ

把/usr/local/mysql修改成相應的mysql目錄。

#mysq.server的部分代碼

mysqld_pid_file_path=

if test -z "$basedir"

then

basedir=/software/mysql/mysql-5.7.24-linux-glibc2.12-x86_64

bindir=/software/mysql/mysql-5.7.24-linux-glibc2.12-x86_64/bin

if test -z "$datadir"

then

datadir=/software/mysql/mysql-5.7.24-linux-glibc2.12-x86_64/data

sbindir=/software/mysql/mysql-5.7.24-linux-glibc2.12-x86_64/bin

libexecdir=/software/mysql/mysql-5.7.24-linux-glibc2.12-x86_64/bin

else

bindir="$basedir/bin"

if test -z "$datadir"

then

datadir="$basedir/data"

sbindir="$basedir/sbin"

libexecdir="$basedir/libexec"

3、mysql配置主從

在master服務器上(3307端口)輸入

show master status;

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

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

| mysql-bin.000006 | 154 | | | |

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

1 row in set (0.13 sec)

在slave服務器上(3308和3309端口)輸入

stop slave;

change master to master_host='localhost', master_user='root', master_password='root',master_log_file='mysql-bin.000006',master_log_pos=154,master_port=3307;

start slave;

測試：

在從服務器上輸入show slave status;

mysql> show slave status;

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

| Waiting for master to send event | localhost | root | 3307 | 60 | mysql-bin.000006 | 154 | iz2zeaf5jdjve80rjlsjgnz-relay-bin.000011 | 367 | mysql-bin.000004 | Yes | No | | | | | | | 1008 | Error 'Can't drop database 'asd'; database doesn't exist' on query. Default database: 'asd'. Query: 'DROP DATABASE `asd`' | 0 | 154 | 3043 | None | | 0 | No | | | | | | NULL | No | 0 | | 1008 | Error 'Can't drop database 'asd'; database doesn't exist' on query. Default database: 'asd'. Query: 'DROP DATABASE `asd`' | | 1 | 5805917d-0e17-11ea-97cd-00163e0c5978 | /software/mysql/mysql3308/data/master.info | 0 | NULL | | 86400 | | | 191124 09:51:23 | | | | | 0 | | | |

1 row in set (0.88 sec)

其中

Slave_IO_StateWaiting for master to send event

Slave_IO_RunningYes

Slave_SQL_RunningYes

表示主從運行成功。

4、mysql運行

使用mysql.server運行mysql實例

[root@iz2zeaf5jdjve80rjlsjgnz bin]# cd /software/mysql/mysql3307/support-files

[root@iz2zeaf5jdjve80rjlsjgnz support-files]# ./mysql.server start

Starting MySQL.Logging to '/software/mysql/mysql3307/data/iz2zeaf5jdjve80rjlsjgnz.err'.

... [ OK ]

進入mysql控制臺

[root@iz2zeaf5jdjve80rjlsjgnz support-files]# cd /software/mysql/mysql-5.7.24-linux-glibc2.12-x86_64/bin

[root@iz2zeaf5jdjve80rjlsjgnz bin]# ./mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.7.24-log

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

#修改默認密碼

mysql> set password = password('root');

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

#設置遠程訪問

mysql> use mysql;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> update user set host = '%' where user = 'root';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

#刷新數據庫

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

mysql> exit;

Bye

[root@iz2zeaf5jdjve80rjlsjgnz bin]#

使用navicat測試連接

5、amoeba安裝

使用wget下載amoeba

//創建目錄

mkdir /software/amoeba

cd /software/amoeba

wget https://jaist.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/2.x/amoeba-mysql-binary-2.0.1-BETA.tar.gz

使用tar命令解壓

tar xf amoeba-mysql-binary-2.0.1-BETA.tar.gz -C /software/amoeba

6、amoeba配置

amoeba配置主要是amoeba.xml和dbServers.xml兩個文件。

amoeba.xml

8066

${clientConnectioneManager}

128

amoeba

${amoeba.home}/conf/access_list.conf

localhost

true

${clientConnectioneManager}

500

com.meidusa.amoeba.net.ConnectionManager

com.meidusa.amoeba.net.AuthingableConnectionManager

${amoeba.home}/conf/dbServers.xml

${amoeba.home}/conf/rule.xml

${amoeba.home}/conf/ruleFunctionMap.xml

${amoeba.home}/conf/functionMap.xml

1500

master

multiPool

true

dbServers.xml

${defaultManager}

128

3307

amoeba

root

500

600000

true

localhost

3307

localhost

3308

localhost

3309

slave1,slave2

進入三個mysql實例。

輸入命令，其中的root應該設置為dbServers.xml中設置的用戶名和密碼。

grant all on *.* to'root'@'%' identified by 'root';

flush privileges;

7、amoeba運行

在控制臺輸入

[root@iz2zeaf5jdjve80rjlsjgnz support-files]# /software/amoeba/bin/amoeba

amoeba start|stop

出現amoeba start|stop說明安裝成功。

輸入/software/amoeba/bin/amoeba start運行

[root@iz2zeaf5jdjve80rjlsjgnz support-files]# /software/amoeba/bin/amoeba start

log4j:WARN log4j config load completed from file:/software/amoeba/conf/log4j.xml

log4j:WARN ip access config load completed from file:/software/amoeba/conf/access_list.conf

2019-11-24 09:44:54,342 INFO net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066.

2019-11-24 09:44:54,344 INFO net.ServerableConnectionManager - Amoeba Monitor Server listening on localhost/127.0.0.1:24211.

使用navicat連接8066端口

測試主從

連接到8066端口

測試主從復制

創建數據庫

mysql> create database rlxy93;

Query OK, 1 row affected (0.12 sec)

創建表，插入數據

mysql> use rlxy93;

Database changed

mysql> CREATE TABLE `admin` (

`user` char(100) DEFAULT NULL,

`password` char(100) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.32 sec)

mysql> insert into admin values('a','a');

Query OK, 1 row affected (0.28 sec)

測試讀寫分離

讀取

關閉master服務器

kill -9 20585 21308

在8066端口測試

mysql> insert into admin values('e','e');

2013 - Lost connection to MySQL server during query

mysql> select * from admin;

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

| user | password |

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

| a | a |

| b | b |

| c | c |

| d | d |

| e | e |

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

5 rows in set (0.59 sec)

此時，寫入失敗，讀取正常。

寫入

關閉兩個slave服務器

kill -9 2772 9260 10010 12338

在8066端口測試

mysql> insert into admin values('r','r');

Query OK, 1 row affected (0.81 sec)

mysql> select * from admin;

2013 - Lost connection to MySQL server during query

此時，讀取失敗，寫入正常。

踩坑

建立三個mysql服務器，一定要配置

log-bin=mysql-bin //日志文件

server-id = 2 //服務器id，不要和其他的相同

replicate-do-db=test //同步的數據庫，可以不指定，默認是所有數據庫

log-slave-updates

sync_binlog=1

slave-net-timeout=60

在使用show slave status;命令時，要有提示下面的，才會開啟主從同步。

Slave_IO_StateWaiting for master to send event

Slave_IO_RunningYes

Slave_SQL_RunningYes

同時，要關注Replicate_Do_DB的配置，比如配置的是test，那么就要在master創建一個test數據庫，之后的同步操作才會在test里面進行，不會在其他數據庫里面執行。

常見問題

提示

Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

刪除目錄下的.lock文件，刪除tmp目錄下的.sock和.lock文件。重啟mysql。

使用mysq.server時報錯

Starting MySQL.Logging to '/software/mysql/mysql-5.7.24-linux-glibc2.12-x86_64/data/iz2zeaf5jdjve80rjlsjgnz.err'.

...The server quit without updating PID file (/software/mys[FAILED]-5.7.24-linux-glibc2.12-x86_64/mysqld.pid).

打開/software/mysql/mysql-5.7.24-linux-glibc2.12-x86_64/data/iz2zeaf5jdjve80rjlsjgnz.err文件

2019-11-24T01:09:45.425848Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.

2019-11-24T01:09:45.425976Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.

2019-11-24T01:09:45.426032Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled

2019-11-24T01:09:45.426073Z 0 [Note] /software/mysql/mysql-5.7.24-linux-glibc2.12-x86_64/bin/mysqld (mysqld 5.7.24-log) starting as process 8143 ...

2019-11-24T01:09:45.541535Z 0 [Note] InnoDB: PUNCH HOLE support available

2019-11-24T01:09:45.541595Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2019-11-24T01:09:45.541605Z 0 [Note] InnoDB: Uses event mutexes

2019-11-24T01:09:45.541610Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier

2019-11-24T01:09:45.541616Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11

2019-11-24T01:09:45.541621Z 0 [Note] InnoDB: Using Linux native AIO

2019-11-24T01:09:45.541642Z 0 [Note] InnoDB: Adjusting innodb_buffer_pool_instances from 8 to 1 since innodb_buffer_pool_size is less than 1024 MiB

2019-11-24T01:09:45.544101Z 0 [Note] InnoDB: Number of pools: 1

2019-11-24T01:09:45.544321Z 0 [Note] InnoDB: Using CPU crc32 instructions

2019-11-24T01:09:45.547333Z 0 [Note] InnoDB: Initializing buffer pool, total size = 256M, instances = 1, chunk size = 128M

2019-11-24T01:09:45.575769Z 0 [Note] InnoDB: Completed initialization of buffer pool

2019-11-24T01:09:45.583452Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().

2019-11-24T01:09:45.593324Z 0 [Note] InnoDB: The first innodb_system data file 'ibdata1' did not exist. A new tablespace will be created!

2019-11-24T01:09:45.593949Z 0 [Note] InnoDB: Setting file '/software/mysql/mysql-5.7.24-linux-glibc2.12-x86_64/data/ibdata1' size to 12 MB. Physically writing the file full; Please wait ...

2019-11-24T01:09:45.618878Z 0 [Note] InnoDB: File '/software/mysql/mysql-5.7.24-linux-glibc2.12-x86_64/data/ibdata1' size is now 12 MB.

2019-11-24T01:09:45.619435Z 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 128 MB

2019-11-24T01:09:45.619604Z 0 [Note] InnoDB: Progress in MB:

100

2019-11-24T01:09:46.448502Z 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 128 MB

2019-11-24T01:09:46.448680Z 0 [Note] InnoDB: Progress in MB:

100

2019-11-24T01:09:47.357744Z 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0

2019-11-24T01:09:47.357907Z 0 [Warning] InnoDB: New log files created, LSN=45790

2019-11-24T01:09:47.357961Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables

2019-11-24T01:09:47.358115Z 0 [Note] InnoDB: Setting file '/software/mysql/mysql-5.7.24-linux-glibc2.12-x86_64/data/ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

2019-11-24T01:09:47.444395Z 0 [Note] InnoDB: File '/software/mysql/mysql-5.7.24-linux-glibc2.12-x86_64/data/ibtmp1' size is now 12 MB.

2019-11-24T01:09:47.444697Z 0 [Note] InnoDB: Doublewrite buffer not found: creating new

2019-11-24T01:09:47.539208Z 0 [Note] InnoDB: Doublewrite buffer created

2019-11-24T01:09:47.546333Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.

2019-11-24T01:09:47.546365Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.

2019-11-24T01:09:47.546557Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2019-11-24T01:09:47.547190Z 0 [Note] InnoDB: Foreign key constraint system tables created

2019-11-24T01:09:47.547220Z 0 [Note] InnoDB: Creating tablespace and datafile system tables.

2019-11-24T01:09:47.547363Z 0 [Note] InnoDB: Tablespace and datafile system tables created.

2019-11-24T01:09:47.547383Z 0 [Note] InnoDB: Creating sys_virtual system tables.

2019-11-24T01:09:47.547476Z 0 [Note] InnoDB: sys_virtual table created

2019-11-24T01:09:47.547626Z 0 [Note] InnoDB: Waiting for purge to start

2019-11-24T01:09:47.597783Z 0 [Note] InnoDB: 5.7.24 started; log sequence number 0

2019-11-24T01:09:47.598264Z 0 [Note] Plugin 'FEDERATED' is disabled.

mysqld: Table 'mysql.plugin' doesn't exist

2019-11-24T01:09:47.598481Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.

2019-11-24T01:09:47.602911Z 0 [Note] Salting uuid generator variables, current_pid: 8143, server_start_time: 1574557785, bytes_sent: 0,

2019-11-24T01:09:47.603045Z 0 [Note] Generated uuid: '1b96c3e3-0e57-11ea-8e76-00163e0c5978', server_start_time: 2292050736929437736, bytes_sent: 68790256

2019-11-24T01:09:47.603068Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 1b96c3e3-0e57-11ea-8e76-00163e0c5978.

2019-11-24T01:09:47.604341Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2019-11-24T01:09:47.605443Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key

2019-11-24T01:09:47.605480Z 0 [Note] Server hostname (bind-address): '*'; port: 3310

2019-11-24T01:09:47.605566Z 0 [Note] IPv6 is available.

2019-11-24T01:09:47.605582Z 0 [Note] - '::' resolves to '::';

2019-11-24T01:09:47.605695Z 0 [Note] Server socket created on IP: '::'.

2019-11-24T01:09:47.607400Z 0 [ERROR] /software/mysql/mysql-5.7.24-linux-glibc2.12-x86_64/bin/mysqld: Can't create/write to file '/software/mysql/mysql-5.7.24-linux-glibc2.12-x86_64/mysqld.pid' (Errcode: 13 - Permission denied)

2019-11-24T01:09:47.607417Z 0 [ERROR] Can't start server: can't create PID file: Permission denied

找到[ERROR]行，這里的錯誤是沒有權限

amoeba找不到JAVA_HOME

在amoeba文件中第一行添加

JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.232.b09-0.el7_7.x86_64/jre

后面跟著的是jdk的路徑。

amoeba運行時提示The stack size specified is too small, Specify at least 160k.

在amoeba文件中修改

DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss256k"

總結

以上是生活随笔為你收集整理的amoeba mysql_从零开始：Mysql基于Amoeba的集群搭建的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： mac虚拟机改显存_虚拟机mac怎么增大
下一篇： MySQL数据库安全配置规范操作