MYSQL教程MySQL的集群配置的基本命令使用及一次操作过程实录
《MYSQL教程MySQL的集群配置的基本命令使用及一次操作过程实录》要点: 1. 先了解一下你是否应该用MySQL集群.MYSQL学习 减少数据中心结点压力和大数据量处理,采用把MySQL分布,一个或多个application对应一个MySQL数据库.把几个MySQL数据库公用的数据做出共享数据,例如购物车,用户对象等等,存在数据结点里面.其他不共享的数据还维持在各自分布的MySQL数据库本身中. 2. 集群MySQL中名称概念.(如上图)MYSQL学习 1)Sql结点(SQL node--上图对应为MySQLd):分布式数据库.包括自身数据和查询中心结点数据.MYSQL学习 2)数据结点(Data node -- ndbd):集群共享数据(内存中).MYSQL学习 3)管理服务器(Management Server C ndb_mgmd):集群管理SQL node,Data node.MYSQL学习 3.配置MYSQL学习 MySQL-max版本,当然现在MySQL集群系统windonws平台上面不被支持.MYSQL学习 安装MySQL就不多说了,网上一大堆,简明扼要.MYSQL学习
当然,你也可以让一个机器同时为3者.MYSQL学习 A,B my.inf加上:MYSQL学习 [MySQLD] ndbcluster # run NDB engine ndb-connectstring=192.168.1.251 # location of MGM node # Options for ndbd process: [MySQL_CLUSTER] ndb-connectstring=192.168.1.251 # location of MGM node A: /var/lib/MySQL-cluster/config.ini [NDBD DEFAULT] NoOfReplicas=1 # Number of replicas DataMemory=80M # How much memory to allocate for data storage IndexMemory=18M # How much memory to allocate for index storage # For DataMemory and IndexMemory,we have used the # default values. Since the "world" database takes up # only about 500KB,this should be more than enough for # this example Cluster setup. # TCP/IP options: [TCP DEFAULT] portnumber=2202 # This the default; however,you can use any # port that is free for all the hosts in cluster # Note: It is recommended beginning with MySQL 5.0 that # you do not specify the portnumber at all and simply allow # the default value to be used instead # Management process options: [NDB_MGMD] hostname=192.168.1.251 # Hostname or IP address of MGM node datadir=/var/lib/MySQL-cluster # Directory for MGM node logfiles # Options for data node "A": [NDBD] # (one [NDBD] section per data node) hostname=192.168.1.251 # Hostname or IP address datadir=/usr/local/MySQL/data # Directory for this data node's datafiles # SQL node options: [MySQLD] hostname=192.168.1.254 #[MySQLD] #这个相当于192.168.1.251 ? 在管理服务器上面(这里是192.168.1.251):MYSQL学习 shell>ndb_mgmd -f /var/lib/MySQL-cluster/config.ini 在数据结点服务器上面(依然是192.168.1.251and more):MYSQL学习 shell>ndbd--initial ?(第一次时加--initial参数) shell>MySQLd & 在251上面察看MYSQL学习 ./ndb_mgm ? -- NDB Cluster -- Management Client -- ndb_mgm> show Connected to Management Server at: 192.168.1.251:1186 Cluster Configuration --------------------- [ndbd(NDB)] 1 node(s) id=2 @192.168.1.251 (Version:5.0.22,Nodegroup: 0,Master) [ndb_mgmd(MGM)] 1 node(s) id=1 @192.168.1.251 (Version:5.0.22) [MySQLd(API)] 1 node(s) id=3 @192.168.1.254 (Version:5.0.22) ok 关闭集群:MYSQL学习 shell>ndb_mgm -e shutdown 5.基本的集群说明MYSQL学习 1)在MySQL集群中.当table引擎为NDBCLUSTER时才做集群,其他非NDBCLUSTER表和一般MySQL数据库表一样,不会共享数据. NDBCLUSTER表数据存储在Data node服务器内存中,Data Node可以为1台或多台服务器,它们之间存放共享数据.Data Node服务器可以分组数据copy.MYSQL学习 例如:2,3,4,5为四台Data Node服务器ID. 2,3为组0. 4,5为组1. 2,3维持数据相同,5维持数据相同. 组0和组1维持数据不同.MYSQL学习 2)sql node服务器中,非NDBCLUSTER数据存在本身数据库中,table引擎为NDBCLUSTER时,数据存储在Data Node中.当查询NDBCLUSTER表时,它会从Data node集群中提起数据.MYSQL学习 3)Manager serverMYSQL学习 管理SQl node和Data node状态.MYSQL学习 附:MySQL集群配置详细过程录制 [root@sqltest01 u01]# service iptables status iptables: Firewall is not running. 如果开启的,请使用service iptables stop [root@sqltest01 u01]# groupadd mysql [root@sqltest01 u01]# useradd -r -g mysql mysql [root@sqltest01 u01]# mkdir -p /usr/local/mysql [root@sqltest01 u01]# chown -R mysql.mysql 2、mysql cluster [root@sqltest01 u01]# tar -zxvf mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64.tar.gz mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64/mysql-test/include/have_plugin_auth.inc mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64/mysql-test/include/kill_query.inc mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64/mysql-test/include/unsafe_binlog.inc mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64/mysql-test/include/have_multi_ndb.inc mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64/mysql-test/include/ipv6_clients.inc mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64/mysql-test/include/setup_fake_relay_log.inc mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64/mysql-test/include/wait_for_slave_sql_error_and_skip.inc ...................................................................................................... 解压后,里面包括了数据库文件以及集群软件 [root@sqltest03 mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64]# pwd /u01/mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64 [root@sqltest03 mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64]# mv * /usr/local/mysql/ --创建集群目录 [root@sqltest03 u01]# cd /usr/local/mysql [root@sqltest03 mysql]# mkdir mysql-cluster [root@sqltest03 mysql]# pwd /usr/local/mysql [root@sqltest03 mysql]# cp bin/ndb_mgm* /usr/local/bin/ [root@sqltest03 mysql]# cd /var/lib [root@sqltest03 mysql]# mkdir mysql-cluster [root@sqltest03 mysql]# cd mysql-cluster [root@sqltest03 mysql]# vi config.ini 配置内容如下: [root@sqltest03 mysql-cluster]# cat config.ini [ndbd default] NoOfReplicas=1 DataMemory=2048M IndexMemory=512M [tcp default] [ndb_mgmd] hostname=192.168.9.243 datadir=/var/lib/mysql-cluster NodeId=1 [ndbd] hostname=192.168.9.241 datadir=/u01/mysql/data NodeId=2 [ndbd] hostname=192.168.9.242 datadir=/u01/mysql/data NodeId=3 [mysqld] hostname=192.168.9.241 NodeId=4 [mysqld] hostname=192.168.9.242 NodeId=5 配置说明: [root@sqltest03 bin]# pwd /usr/local/bin [root@sqltest03 bin]# ./ndb_mgmd -f /var/lib/mysql-cluster/config.ini MySQL Cluster Management Server mysql-5.6.21 ndb-7.3.7 进入执行查看 [root@sqltest03 bin]# ndb_mgm -- NDB Cluster -- Management Client -- ndb_mgm> show Connected to Management Server at: localhost:1186 Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=2 (not connected,accepting connect from 192.168.9.241) id=3 (not connected,accepting connect from 192.168.9.242) [ndb_mgmd(MGM)] 1 node(s) id=1 @192.168.9.243 (mysql-5.6.21 ndb-7.3.7) [mysqld(API)] 2 node(s) id=4 (not connected,accepting connect from 192.168.9.241) id=5 (not connected,accepting connect from 192.168.9.242) 可以看到有两个节点,节点没有连接上 [root@sqltest01 mysql]# mkdir -p /usr/local/mysql --用于存放刚才解压的文件,如mysql的bin目录等 [root@sqltest01 mysql]# mkdir -p /u01/mysql/data --用于存储数据文件(innodb) [root@sqltest01 mysql]# chown -R mysql.mysql /u01 --将先前解压的文件拷贝 [root@sqltest01 mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64]# pwd /u01/mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64 [root@sqltest01 mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64]# mv * /usr/local/mysql/ [root@sqltest01 mysql]# chown -R mysql.mysql /usr/local/mysql/ --拷贝mysql.server [root@sqltest01 support-files]# pwd /usr/local/mysql/support-files [root@sqltest01 support-files]# ls -lrt total 32 -rw-r--r--. 1 mysql mysql 773 Oct 9 21:46 magic -rwxr-xr-x. 1 mysql mysql 10880 Oct 9 22:42 mysql.server -rwxr-xr-x. 1 mysql mysql 894 Oct 9 22:42 mysql-log-rotate -rwxr-xr-x. 1 mysql mysql 1061 Oct 9 22:42 mysqld_multi.server -rw-r--r--. 1 mysql mysql 1126 Oct 9 22:42 my-default.cnf -rwxr-xr-x. 1 mysql mysql 1153 Oct 9 22:42 binary-configure [root@sqltest01 support-files]# cp mysql.server /etc/rc.d/init.d/mysqld --编辑环境变量 [root@sqltest01 tmp]# vi /etc/profile 添加如下: PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH export PATH [root@sqltest01 tmp]# source /etc/profile ?? --使修改生效 [root@sqltest01 support-files]# cp my-default.cnf /etc/my.cnf 并对my.cnf进行配置,具体配置如下 [mysqld] ndbcluster basedir=/usr/local/mysql datadir=/u01/mysql/data port=3306 [mysql_cluster] ndb-connectstring=192.168.9.243 --初始化节点数据库 [root@sqltest01 mysql]# scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/u01/mysql/data 执行完这条命令以后,数据库的数据文件(包括mysql,test,performance_schema等数据库),就安装到相应的位置了,可以使用了 [root@sqltest01 mysql]# ndbd --initial 2014-12-24 17:55:57 [ndbd] INFO -- Angel connected to '192.168.9.243:1186' 2014-12-24 17:55:57 [ndbd] INFO -- Angel allocated nodeid: 2 第一次启动时,需要加--initial来初始化数据节点,第二次启动时,就不需要这个参数了 ndb_mgm> show Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=2 @192.168.9.241 (mysql-5.6.21 ndb-7.3.7,starting,Nodegroup: 0) --表明已经连接上了 id=3 (not connected,accepting connect from 192.168.9.242) 启动mysqld [root@sqltest01 mysql]# cd /usr/local/mysql/bin [root@sqltest01 bin]# ./mysqld_safe --user=mysql 141224 17:59:50 mysqld_safe Logging to '/u01/mysql/data/sqltest01.err'. 141224 17:59:51 mysqld_safe Starting mysqld daemon with databases from /u01/mysql/data 启动数据库时,第一次初始化使用的root,而这次使用mysql,需要对/u01/mysql/data权限进行配置,否则报不可读写 ndb_mgm> show Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=2 @192.168.9.241 (mysql-5.6.21 ndb-7.3.7,*) id=3 (not connected,accepting connect from 192.168.9.242) [ndb_mgmd(MGM)] 1 node(s) id=1 @192.168.9.243 (mysql-5.6.21 ndb-7.3.7) [mysqld(API)] 2 node(s) id=4 @192.168.9.241 (mysql-5.6.21 ndb-7.3.7) ????????????????? --表明已经连接上了 ndb_mgm> show Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=2 @192.168.9.241 (mysql-5.6.21 ndb-7.3.7,*) id=3 @192.168.9.242 (mysql-5.6.21 ndb-7.3.7,Nodegroup: 1) [ndb_mgmd(MGM)] 1 node(s) id=1 @192.168.9.243 (mysql-5.6.21 ndb-7.3.7) [mysqld(API)] 2 node(s) id=4 @192.168.9.241 (mysql-5.6.21 ndb-7.3.7) id=5 @192.168.9.242 (mysql-5.6.21 ndb-7.3.7) 6、在两个节点测试 [root@sqltest01 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.6.21-ndb-7.3.7-cluster-gpl MySQL Cluster Community Server (GPL) Copyright (c) 2000,2014,Oracle and/or its affiliates. All rights reserved. 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> create database mydb1; Query OK,1 row affected (0.07 sec) mysql> use mydb1; Database changed mysql> create table mytb1(id int,birthdate datetime,pername char(10)) engine=ndbcluster; Query OK,0 rows affected (0.19 sec) mysql> insert into mytb1(id,birthdate,pername) values(1,'2013-01-23 09:45:10','pengzitong'); Query OK,1 row affected (0.00 sec) mysql> commit; Query OK,0 rows affected (0.00 sec) mysql> insert into mytb1(id,pername) values(2,'2007-07-09 09:45:10','pengzixin'); Query OK,1 row affected (0.00 sec) mysql> commit; Query OK,0 rows affected (0.00 sec) 在第二节点检查 [root@sqltest02 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.6.21-ndb-7.3.7-cluster-gpl MySQL Cluster Community Server (GPL) Copyright (c) 2000,Oracle and/or its affiliates. All rights reserved. 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> use mydb1 mysql> select * from mytb1; +------+---------------------+------------+ | id | birthdate | pername | +------+---------------------+------------+ | 1 | 2013-01-23 09:45:10 | pengzitong | | 2 | 2007-07-09 09:45:10 | pengzixin | +------+---------------------+------------+ 7、集群停止 [root@sqltest03 bin]# /usr/local/mysql/ndb_mgm -e shutdown 运行以下命令关闭SQL节点的mysqld服务: [root@sqltest01 bin]# /usr/local/mysql/bin/mysqladmin -uroot shutdown 编程之家PHP培训学院每天发布《MYSQL教程MySQL的集群配置的基本命令使用及一次操作过程实录》等实战技能,PHP、MYSQL、LINUX、APP、JS,CSS全面培养人才。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |