MariaDB Galera Cluster：Install on CentOS 7
相关网站资源：
https://downloads.mariadb.org/mariadb/
https://downloads.mariadb.org/mariadb/repositories/#mirror=neusoft&distro=RedHat&distro_release=rhe[……]
Category Archives: MySQL
MySQL，查询结果集，中文乱码
有时候MySQL查询数据的时候会出现中文乱码的现象，如下：
查看一下表创建时候的字符集：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> show create table factory -> ; +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | factory | CREATE TABLE `factory` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `code` varchar(255) DEFAULT NULL, `is_disabled` bit(1) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 | +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> |
查看当前列的信息：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> show full columns from factory; +-------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+ | id | bigint(20) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | | code | varchar(255) | utf8_general_ci | YES | | NULL | | select,insert,update,references | | | is_disabled | bit(1) | NULL | YES | | NULL | | select,insert,update,references | | | name | varchar(255) | utf8_general_ci | YES | | NULL | | select,insert,update,references | | +-------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+ 4 rows in set (0.00 sec) mysql> |
查看环境变量：
[cray[……]
MySQL 5.6.17（On CentOS 7） error：FATAL ERROR: please install the following Perl modules before executing /usr/bin/mysql_install_db: Data::Dumper
该错误的详情如下：
|
1 2 3 4 |
[root@mysql3 shell]# mysql_install_db FATAL ERROR: please install the following Perl modules before executing /usr/bin/mysql_install_db: Data::Dumper [root@mysql3 shell]# |
解法：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
[root@mysql3 shell]# yum install -y autoconf Loaded plugins: fastestmirror, langpacks base | 3.6 kB 00:00:00 extras | 3.4 kB 00:00:00 updates | 3.4 kB 00:00:00 Loading mirror speeds from cached hostfile * base: mirrors.163.com * extras: mirrors.cn99.com * updates: mirrors.zju.edu.cn Resolving Dependencies --> Running transaction check ---> Package autoconf.noarch 0:2.69-11.el7 will be installed --> Processing Dependency: m4 >= 1.4.14 for package: autoconf-2.69-11.el7.noarch --> Processing Dependency: perl(Data::Dumper) for package: autoconf-2.69-11.el7.noarch --> Running transaction check ---> Package m4.x86_64 0:1.4.16-10.el7 will be installed ---> Package perl-Data-Dumper.x86_64 0:2.145-3.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================================================= Package Arch Version Repository Size ================================================================================================================= Installing: autoconf noarch 2.69-11.el7 ISO 701 k Installing for dependencies: m4 x86_64 1.4.16-10.el7 base 256 k perl-Data-Dumper x86_64 2.145-3.el7 ISO 47 k Transaction Summary ================================================================================================================= Install 1 Package (+2 Dependent packages) Total download size: 1.0 M Installed size: 2.8 M Downloading packages: (1/3): autoconf-2.69-11.el7.noarch.rpm | 701 kB 00:00:00 (2/3): perl-Data-Dumper-2.145-3.el7.x86_64.rpm | 47 kB 00:00:00 warning: /var/cache/yum/x86_64/7/base/packages/m4-1.4.16-10.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY Public key for m4-1.4.16-10.el7.x86_64.rpm is not installed (3/3): m4-1.4.16-10.el7.x86_64.rpm | 256 kB 00:00:01 ----------------------------------------------------------------------------------------------------------------- Total 625 kB/s | 1.0 MB 00:00:01 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7 Importing GPG key 0xF4A80EB5: Userid : "CentOS-7 Key (CentOS 7 Official Signing Key) <security@centos.org>" Fingerprint: 6341 ab27 53d7 8a78 a7c2 7bb1 24c6 a8a7 f4a8 0eb5 Package : centos-release-7-1.1503.el7.centos.2.8.x86_64 (@anaconda) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7 Running transaction check Running transaction test Transaction test succeeded Running transaction Warning: RPMDB altered outside of yum. ** Found 2 pre-existing rpmdb problem(s), 'yum check' output follows: 2:postfix-2.10.1-6.el7.x86_64 has missing requires of libmysqlclient.so.18()(64bit) 2:postfix-2.10.1-6.el7.x86_64 has missing requires of libmysqlclient.so.18(libmysqlclient_18)(64bit) Installing : m4-1.4.16-10.el7.x86_64 1/3 Installing : perl-Data-Dumper-2.145-3.el7.x86_64 2/3 Installing : autoconf-2.69-11.el7.noarch 3/3 Verifying : perl-Data-Dumper-2.145-3.el7.x86_64 1/3 Verifying : m4-1.4.16-10.el7.x86_64 2/3 Verifying : autoconf-2.69-11.el7.noarch 3/3 Installed: autoconf.noarch 0:2.69-11.el7 Dependency Installed: m4.x86_64 0:1.4.16-10.el7 perl-Data-Dumper.x86_64 0:2.145-3.el7 Complete! [root@mysql3 shell]# |
再次尝试：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
[root@mysql3 shell]# mysql_install_db Installing MySQL system tables...2017-08-11 19:01:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2017-08-11 19:01:00 18268 [Note] InnoDB: Using atomics to ref count buffer pool pages 2017-08-11 19:01:00 18268 [Note] InnoDB: The InnoDB memory heap is disabled 2017-08-11 19:01:00 18268 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2017-08-11 19:01:00 18268 [Note] InnoDB: Compressed tables use zlib 1.2.3 2017-08-11 19:01:00 18268 [Note] InnoDB: Using Linux native AIO 2017-08-11 19:01:00 18268 [Note] InnoDB: Using CPU crc32 instructions 2017-08-11 19:01:00 18268 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2017-08-11 19:01:00 18268 [Note] InnoDB: Completed initialization of buffer pool 2017-08-11 19:01:00 18268 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created! 2017-08-11 19:01:00 18268 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB 2017-08-11 19:01:00 18268 [Note] InnoDB: Database physically writes the file full: wait... 2017-08-11 19:01:01 18268 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB 2017-08-11 19:01:02 18268 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB 2017-08-11 19:01:03 18268 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 2017-08-11 19:01:03 18268 [Warning] InnoDB: New log files created, LSN=45781 2017-08-11 19:01:03 18268 [Note] InnoDB: Doublewrite buffer not found: creating new 2017-08-11 19:01:03 18268 [Note] InnoDB: Doublewrite buffer created 2017-08-11 19:01:03 18268 [Note] InnoDB: 128 rollback segment(s) are active. 2017-08-11 19:01:03 18268 [Warning] InnoDB: Creating foreign key constraint system tables. 2017-08-11 19:01:03 18268 [Note] InnoDB: Foreign key constraint system tables created 2017-08-11 19:01:03 18268 [Note] InnoDB: Creating tablespace and datafile system tables. 2017-08-11 19:01:03 18268 [Note] InnoDB: Tablespace and datafile system tables created. 2017-08-11 19:01:03 18268 [Note] InnoDB: Waiting for purge to start 2017-08-11 19:01:03 18268 [Note] InnoDB: 5.6.17 started; log sequence number 0 2017-08-11 19:01:04 18268 [Note] Binlog end 2017-08-11 19:01:04 18268 [Note] InnoDB: FTS optimize thread exiting. 2017-08-11 19:01:04 18268 [Note] InnoDB: Starting shutdown... 2017-08-11 19:01:05 18268 [Note] InnoDB: Shutdown completed; log sequence number 1625977 OK Filling help tables...2017-08-11 19:01:05 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2017-08-11 19:01:05 18310 [Note] InnoDB: Using atomics to ref count buffer pool pages 2017-08-11 19:01:05 18310 [Note] InnoDB: The InnoDB memory heap is disabled 2017-08-11 19:01:05 18310 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2017-08-11 19:01:05 18310 [Note] InnoDB: Compressed tables use zlib 1.2.3 2017-08-11 19:01:05 18310 [Note] InnoDB: Using Linux native AIO 2017-08-11 19:01:05 18310 [Note] InnoDB: Using CPU crc32 instructions 2017-08-11 19:01:05 18310 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2017-08-11 19:01:05 18310 [Note] InnoDB: Completed initialization of buffer pool 2017-08-11 19:01:05 18310 [Note] InnoDB: Highest supported file format is Barracuda. 2017-08-11 19:01:05 18310 [Note] InnoDB: 128 rollback segment(s) are active. 2017-08-11 19:01:05 18310 [Note] InnoDB: Waiting for purge to start 2017-08-11 19:01:05 18310 [Note] InnoDB: 5.6.17 started; log sequence number 1625977 2017-08-11 19:01:05 18310 [Note] Binlog end 2017-08-11 19:01:05 18310 [Note] InnoDB: FTS optimize thread exiting. 2017-08-11 19:01:05 18310 [Note] InnoDB: Starting shutdown... 2017-08-11 19:01:07 18310 [Note] InnoDB: Shutdown completed; log sequence number 1625987 OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h mysql3 password 'new-password' Alternatively you can run: /usr/bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd /usr ; /usr/bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd mysql-test ; perl mysql-test-run.pl Please report any problems at http://bugs.mysql.com/ The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://shop.mysql.com New default config file was created as /usr/my.cnf and will be used by default by the server when you start it. You may edit this file to change server settings [root@mysql3 shell]# |
——————————————
Do[……]
Percona：On CentOS 7 卸载
具体如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
[root@mysql1 ~]# rpm -qa | grep Server Percona-Server-client-56-5.6.36-rel82.1.el7.x86_64 Percona-Server-shared-56-5.6.36-rel82.1.el7.x86_64 Percona-Server-server-56-5.6.36-rel82.1.el7.x86_64 [root@mysql1 ~]# [root@mysql1 ~]# rpm -e Percona-Server-server-56-5.6.36-rel82.1.el7.x86_64 [root@mysql1 ~]# [root@mysql1 ~]# rpm -qa | grep Server Percona-Server-client-56-5.6.36-rel82.1.el7.x86_64 Percona-Server-shared-56-5.6.36-rel82.1.el7.x86_64 [root@mysql1 ~]# [root@mysql1 ~]# rpm -e Percona-Server-client-56-5.6.36-rel82.1.el7.x86_64 [root@mysql1 ~]# rpm -e Percona-Server-shared-56-5.6.36-rel82.1.el7.x86_64 error: Failed dependencies: libmysqlclient.so.18()(64bit) is needed by (installed) postfix-2:2.10.1-6.el7.x86_64 libmysqlclient.so.18(libmysqlclient_18)(64bit) is needed by (installed) postfix-2:2.10.1-6.el7.x86_64 [root@mysql1 ~]# [root@mysql1 ~]# rpm -e --nodeps Percona-Server-shared-56-5.6.36-rel82.1.el7.x86_64 warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave [root@mysql1 ~]# [root@mysql1 ~]# rpm -qa | grep Server [root@mysql1 ~]# |
————————
Done。
MySQL：关于列的 NULL 和 NOT NULL
创建一个测试表：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> use test; Database changed mysql> show tables; Empty set (0.00 sec) mysql> create table people -> ( -> id int, -> name varchar(40), -> sex varchar(10), -> comment varchar(900) -> ); Query OK, 0 rows affected (0.35 sec) mysql> |
看看这时候的表结构：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> desc people; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(40) | YES | | NULL | | | sex | varchar(10) | YES | | NULL | | | comment | varchar(900) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ 4 rows in set (0.02 sec) mysql> |
可以看到，这个时候是没有非空约束的，所以，你可以有的列不插入值：
[crayon-59c14f7f368482[……]