MySQL部署

artemisecho

已于 2022-04-11 11:42:23 修改

阅读量529

点赞数

文章标签： mysql

于 2022-04-11 11:40:53 首次发布

本文链接：https://blog.csdn.net/artemisecho/article/details/124095182

版权

ubuntu下安装方式：

# apt-get install mysql-server mysql-client

索引&外键

联合索引要将匹配（=）放在前面，范围（><）放在后面，否则会失效

函数DATE_FORMAT会引起索引失效，导致全表扫描

多表关联查询索引失效问题：关联字段的字符集类型不同；大表（数据量大）的字段进行匹配；条件顺序有时候也会影响

缓存设置

query_cache_type：使用查询缓存的方式【查看：select @@query_cache_type，设置：set session query_cache_type=off/on】，当我们执行 select id,name from tableName就会用到查询缓存。在 query_cache_type 打开的情况下，如果你不想使用缓存，需要指明select sql_no_cache id,name from tableName。

have_query_cache：设置查询缓存是否可用【查看：show variables like 'have_query_cache'】

query_cache_size：设置查询缓存的大小【查看：select @@global.query_cache_size，设置：set @@global.query_cache_size=100000】

query_cache_limit：控制缓存查询结果的最大值，超过此值，查询结果不会缓存【查看：select @@global.query_cache_limit】

编码格式

show VARIABLES like 'character_set%';【latin1是ISO-8859-1的别名，编码范围0x00~0xFF】

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

| Variable_name | Value |

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

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | latin1 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | latin1 |

| character_set_system | utf8 |

| character_sets_dir | /usr/local/mysql/share/charsets/ |

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

执行sql路径：

输入：client->connection->server

输出：server->connection->results

插入数据经过utf8->utf8->latin1的转换，数据中含有\u0000~\u00ff范围外的字符，会因无法在latin1中表示而被转换为“?”。可在my.cnf文件中修改server级编码：character-set-server=utf8

主从配置

主机

# vim /etc/mysql/mysql.conf.d/mysqld.cnf

找到server-id及log_bin字段，去除相关注释，保存

# mysql -p123456

mysql>create user repl; //创建新用户。repl用户必须具有REPLICATION SLAVE权限，除此之外没有必要添加不必要的权限，密码为mysql。说明一下192.168.0.%，这个配置是指明repl用户所在服务器，这里%是通配符，表示192.168.0.0-192.168.0.255的Server都可以以repl用户登陆主服务器。当然你也可以指定固定Ip。

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.20.19.%' IDENTIFIED BY '123456';

mysql> exit

# /etc/init.d/mysql restart

检测：mysql> show master status;

从机

# vim /etc/mysql/mysql.conf.d/mysqld.cnf

找到server-id字段，去除注释并修改server-id=2（递增），保存

# /etc/init.d/mysql restart

# mysql -p123456

mysql>CHANGE MASTER TO  MASTER_HOST='172.20.19.26',
MASTER_USER='repl',  MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000100',  MASTER_LOG_POS=154; 【这两个值从主服务器上获取：show master status】

mysql>start slave;【变更需要先reset slave】

mysql>show slave status;【这两个最重要：Slave_IO_Running:Yes；Slave_SQL_Running:Yes，如出问题，需stop slave，再执行上面的变更操作】

读写分离

编码方式ReplicationDriver

中间件mycat、atlas

Cluster：

A MySQL Cluster is defined as one or more MySQL Servers providing access to an NDBCLUSTER storage engine—that is, to a set of MySQL Cluster data nodes (ndbd processes). There are four main access paths from Java to NDBCLUSTER, listed here:

•JDBC and mysqld. JDBC works by sending SQL statements to the MySQL Server and returning result sets. When using JDBC, you must write the SQL, manage the connection, and copy any data from the result set that you want to use in your program as objects. The JDBC implementation most often used with the MySQL Server is MySQL Connector/J.

•Java Persistence API (JPA) and JDBC. JPA uses JDBC to connect to the MySQL Server. Unlike JDBC, JPA provides an object view of the data in the database.

•ClusterJ. ClusterJ uses a JNI bridge to the NDB API for direct access to NDBCLUSTER. It employs a style of data access that is based on a domain object model, similar in many ways to that employed by JPA. ClusterJ does not depend on the MySQL Server for data access.

•ClusterJPA. ClusterJPA is an adapter for the OpenJPA implementation. It can use either of two different access paths—JDBC or ClusterJ—to MySQL Cluster data, depending on the type of operation to be performed. This can significantly improve performance for some uses, bypassing SQL, JDBC, and the MySQL Server entirely when it is more efficient to do so.

安装mysql cluster，需要安装管理节点、数据节点和SQL节点，具体安装过程：

#tar xzvf mysql-cluster-gpl-7.4.8-linux-glibc2.5-x86_64.tar.gz -C /usr/local/

#mv mysql-cluster-gpl-7.4.8-linux-glibc2.5-x86_64 mysql

#groupadd mysql

#useradd -g mysql mysql

#chown -R mysql data/【data目录：auto.cnf ibdata1 ib_logfile0 ib_logfile1 mysql/ ndbinfo/ performance_schema/ test/ test3.err test3.pid】

#chgrp -R mysql *

#./scripts/mysql_install_db --user=mysql

#cp ./support-files/mysql.server /etc/init.d/mysqld

#cp ./support-files/my-default.cnf /etc/my.cnf

#vim /etc/my.cnf【增加如下内容】

[mysqld]

character-set-server=utf8 #数据库级的字符集

optimizer_switch=engine_condition_pushdown=off

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

max_allowed_packet = 20M #批量插入限制

#设置单表大小，默认16M

tmp_table_size = 2048M

max_heap_table_size = 2048M

ndbcluster #运行NDB存储引擎

ndb-connectstring=10.30.17.33

[mysql_cluster]
ndb-connectstring=10.30.17.33#管理节点IP

#service mysqld start

安装完mysql cluster后，需要给各SQL节点机器添加密码和访问权限：

mysql>grant all on *.* to 'root'@'localhost' IDENTIFIED BY '你的密码'with grant option ;
mysql>flush privileges;

mysql>grant all on *.* to 'root'@'%' IDENTIFIED BY '你的密码'with grant option ;
mysql>flush privileges;

#service mysqld stop

管理节点：

#cp mysql/bin/ndb_mgm* /usr/local/bin

#mkdir /var/lib/mysql-cluster

#vim /var/lib/mysql-cluster/config.ini【配置文件内容如下，DataMemory内存分配4G（总8G）时，启动出现错误：Forced node shutdown completed. Occured during startphase 0. Initiated by signal 11】：

[NDBD DEFAULT]

NoOfReplicas=1

DataMemory=512M

IndexMemory=256M

DataDir=/var/lib/mysql-cluster

SharedGlobalMemory=384M

FragmentLogFileSize=256M

InitFragmentLogFiles=SPARSE

NoOfFragmentLogFiles=8

RedoBuffer=256M

MaxNoOfTables=1024

MaxNoOfAttributes=5000000

MaxNoOfOrderedIndexes=10000

MaxNoOfConcurrentTransactions=16384

MaxNoOfLocalOperations=100000

MaxNoOfConcurrentOperations=100000

[TCP DEFAULT]

portnumber=2202

[NDB_MGMD]

hostname=10.30.17.33

datadir=/var/lib/mysql-cluster/

[NDBD]

hostname=10.30.17.33

datadir=/usr/local/mysql/data/

[NDBD]

hostname=10.30.17.85

datadir=/usr/local/mysql/data/

[NDBD]

hostname=10.30.17.94

datadir=/usr/local/mysql/data/

[NDBD]

hostname=10.30.17.15

datadir=/usr/local/mysql/data/

[MYSQLD]

hostname=10.30.17.33

[MYSQLD]

hostname=10.30.17.85

[MYSQLD]

hostname=10.30.17.94

[MYSQLD]

hostname=10.30.17.15

[MYSQLD]

启动顺序：【参看/var/lib/mysql-cluster/ndb_1_cluster.log】

管理节点->数据节点->SQL节点

启动管理节点：

/usr/local/bin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini –initial/--reload

启动节点（所有机器）：

/usr/local/mysql/bin/ndbd --initial

节点管理命令：ndb_mgm

>show【查看】

>startbackup【备份节点数据，可指定节点ID】

>shutdown【关闭所有节点】

>all report memoryusage【查看使用率】

使用mysql cluster中需要注意两点：
一是表必须用ENGINE=NDB或ENGINE=NDBCLUSTER选项创建，使用ALTER TABLE选项更改也可以，以使用NDB Cluster存储引擎在 Cluster内复制它们。
二是每个NDB表必须要有一个主键，如果没有，NDB Cluster存储引擎将自动生成隐含主键。

CREATE TABLE employee (

id INT NOT NULL PRIMARY KEY,

first VARCHAR(64) DEFAULT NULL,

last VARCHAR(64) DEFAULT NULL,

municipality VARCHAR(64) DEFAULT NULL,

started DATE DEFAULT NULL,

ended DATE DEFAULT NULL,

department INT NOT NULL DEFAULT 1,

UNIQUE KEY idx_u_hash (last,first) USING HASH,

KEY idx_municipality (municipality)

) ENGINE=NDBCLUSTER;

NDB 是一个内存式存储引擎，也就是说，他会将所有的数据和索引数据都load 到内存中，但也会将数据持久化到存储设备上。不过，最新版本，已经支持用户自己选择数据可以不全部Load 到内存中了，这对于有些数据量太大或者基于成本考虑而没有足够内存空间来存放所有数据的用户来说的确是一个大好消息。但是索引和索引数据需要存入内存中，内存数据受到配置DataMemory大小的限制。

虽然说是采用磁盘存储表，但是如果表中有主键或者索引就会将索引和该列的数据都存储到内存中，磁盘数据表的变长列将占用固定空间，对于每行来说，也就是占用最大空间。在磁盘数据表中，TEXT和BLOB的前256个字节存于内存，其他的存放在磁盘上。磁盘数据表的每一行要在内存里面存放8个字节，以指向存放在磁盘上的数据。所以如果一行要存放在磁盘上数据少于8个字节，还不如都放在内存里。数据节点重启时--initial选项并不移除磁盘数据文件，需要手工事先把他们干掉（如果原来就建有表空间，--initial参数会将表空间和表空间上的数据清除掉，切记！！）

采用磁盘存储，需要创建日志文件组以及undo日志文件，另外需要添加表空间，以及数据文件。创建出错时可以使用show errors/warnings查看。

CREATE LOGFILE GROUP lg_2 ADD UNDOFILE 'undo_2.log' INITIAL_SIZE 1024M UNDO_BUFFER_SIZE 128M ENGINE NDBCLUSTER;

CREATE TABLESPACE ts_2 ADD DATAFILE 'data_2.dat' USE LOGFILE GROUP lg_2 INITIAL_SIZE 8G EXTENT_SIZE 128M ENGINE NDBCLUSTER;

SELECT TABLESPACE_NAME, FILE_NAME, EXTENT_SIZE*TOTAL_EXTENTS/1024/1024 AS TOTAL_MB, EXTENT_SIZE*FREE_EXTENTS/1024/1024 AS FREE_MB, EXTRA FROM information_schema.FILES WHERE FILE_TYPE="DATAFILE";

ERROR:The table '#sql-5006_4' is full:

alter tablespace ts_2 add datafile 'data_1.dat' initial_size 8G engine ndb;

CREATE TABLE employee1 (

id INT NOT NULL PRIMARY KEY,

first VARCHAR(64) DEFAULT NULL,

last VARCHAR(64) DEFAULT NULL,

municipality VARCHAR(64) DEFAULT NULL,

started DATE DEFAULT NULL,

ended DATE DEFAULT NULL,

department INT NOT NULL DEFAULT 1,

UNIQUE KEY idx_u_hash (last,first) USING HASH,

KEY idx_municipality (municipality)

) tablespace ts_2 storage disk ENGINE=NDBCLUSTER DEFAULT CHARSET=utf8;

磁盘表的varchar列的占据的总长度不能多于8052B，需在配置文件中将MaxNoOfAttributes值增加？【貌似无效，可以改类型为text】

可以在information_schema.FILES和ndbinfo.logspaces里面获取日志文件和数据文件的信息

可以使用set ndb_table_no_logging=1;来创建临时表【the table will not be logged to disk in the RedoLog and will not be dumped in the LCP（文件路径/usr/local/mysql/data/ndb_*_fs/目录D*文件夹及LCP文件夹），可用命令/usr/local/mysql/bin/ndb_desc -d clusterdb 0_0查看】，表建完后再set ndb_table_no_logging=0;只限于内存表（自测）

artemisecho

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL部署

ubuntu下安装方式：# apt-get install mysql-server mysql-client索引&外键联合索引要将匹配（=）放在前面，范围（><）放在后面，否则会失效函数DATE_FORMAT会引起索引失效，导致全表扫描多表关联查询索引失效问题：关联字段的字符集类型不同；大表（数据量大）的字段进行匹配；条件顺序有时候也会影响缓存设置query_cache_type：使用查询缓存的方式【查看：select @@query_cache_t
复制链接

扫一扫