One, the installation configuration on Mac //Brew installation brew Install mysql //set to boot brew services start mysql //can also manually start mysql.server start //login mysql-uroot Note:  1) If you are prompted to log in without the MySQL command, you will need to add the MySQL bin directory to path: Export path= $PATH:/usr/local/mysql/bin 2) log in as default, no password, to set the password, You can run the following command after logging in: set PASSWORD for ' root ' @ ' localhost ' = PASSWORD (' Your PASSWORD '); II, Linux CentOS installation Install CENTOS7 on mysql5.6, installed as source, the process is as follows: 1) Install dependency package: Yum install-y gcc gcc-c++ kernel-devel ncurses-devel Bison cmake  2) command execution Process: # preconfiguration setup shell> groupadd mysql shell> useradd-r-G Mysql-s/bin/false mysql # Beginning of Source-build specific instructions shell> tar ZXVF mysql-version.tar.gz shell> cd mysql-version shell> mkdir build SHELL&G T CD build shell> cmake. shell> make shell> make install # End of Source-build specific instructions& nbsp; # postinstallation setup shell> cd/usr/local/mysql shell> chown-r Mys QL . shell> chgrp-r mysql . shell> scripts/mysql_install_db--user=mysql (This step May cause problems 1) shell> chown-r root . shell> chown-r mysql data shell> bin/mysq Ld_safe--user=mysql & # Next command is optional shell> CP SuppoRT-FILES/MYSQL.SERVER/ETC/INIT.D/MYSQL.SERVER      3) environment variable configuration export path= $PATH:/usr/local/mysql/bin/ 4) set root user password a, view current user and login password: SELECT user, Host, Password from mysql.user; &N Bsp b, reset password: update mysql.user SET Password = Password ( ' 123456 ') where user= ' root '; flush privileges; //Refresh user rights information  C, sign out of re-login: mysql-uroot-p 5) Allow any host to access the database remotely grant all privileges on * * to ' root ' @ '% ' identified by ' 12345 6 ' with GRANT option; III, exception problem and resolution 1, restart error => in my.cnf configuration file [MySQL] under Add tmpdir=/user/username/mysql/data, or delete the/usr/local/mysql/data directory under the logfile* file The following issues mainly occur in the CENTOS7 environment of the installation process: 2, FATAL error:please Install the following Perl modules before executing/usr/local/mysql/scripts/mysql_install_db:data:dumper=> yum-y install Autoconf 3, Mysqld_safe error:log-error set to '/var/log/mariadb/mariadb.log ', however file don ' t exists. Create writable for user ' MySQL '. =>/etc/my.cnf[mysqld_safe]log-error=/var/log/mysql/mysql.logpid-file=/var/run/ Mysql/mysql.pid 4, Can ' t connect to local MySQL server through socket '/tmp/mysql.sock ' (not resolved, It could be that the residue was previously unloaded and the =_=! was re-installed. ) 5, ignoring query to other database start mysql without the-u parameter 6, a word break is truncated, check whether the inserted data type is consistent with the data type in the actual table structure Iv. related commands 1, viewing character set show VARIABLES WHERE variable_name like ' character\_set\_% ' OR variable_ Name like ' collation% '; show full columns from users; //See table field Character Set 2, set character sets, support emoji emoji  UTF8MB4 can be used for emoji, you should set the database, table, table related fields of the character set are UTF8MB4, To achieve normal insertion of data or update set character_set_results= ' utf8mb4 '; //Set table field character set alter table articles MODIFY column title text CHARACTER set utf8mb4; & nbsp; //setting table Character set alter table articles charset=utf8mb4; ALTER TABLE t_cpdaily_emotioncommunicates CONVERT to CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
3, String Common operations 1) Replace replace update articles SET CONTURL =replace (Conturl, '/we ', '/you '), Simgurl=replace (Simgurl, '/root/asse ', '), Resurl=replace (Resurl, '/root/asse ', ') ; 2) cut split //set the Split function to return the specified substring length create FUNCTION substrcount (x varchar ($), Delim varchar) returns Int return ( Length (x)-length (REPLACE (x, Delim, ')))/length (Delim); select DISTINCT BM, bmfull from V_ssxx_jrxy ORDER by BM, Substrcount (bmfull, '/') desc; 3) Substring_index built-in functions, select Level Two select DISTINCT BM, Substring_index (bmfull, '/', -1) as Seconddepart from V_ssxx_jrxy ORDER by bm 4, data Migration & nbsp If the input mysqldump prompt does not have the command, the command is enabled: Export path= $PATH:/usr/local/mysql/bin/mysqldump Export the table structure and data for the entire database: Shell run >>> Mysqldump-uuser-ppassword databasename-h host> db.sql Import: Run in mysqlclient >>> source db.sql 5, full-text index InnoDB requires MySQL 5.6.4 or later version support 1) to create an index that can be made when a table is created, or you can modify the table or directly create an index alter TABLE articles ADD COLUMN seg_content VARCHAR (4000) DEFAULT null; create Fulltext INDEX IDX _seg_content on articles (seg_content); //Query select * From articles WHERE MATCH (seg_content) against (' novelty ' in BOOLEAN MODE); mysql currently supports full-text search and: NATURAL LANGUAGE Model:myisam engine, this mode for word frequency more than 50% words as a stop word 2) operation process, may find that For Chinese, the result is often not searchable, because the default configuration under the value of Innodb_ft_min_token_size is 3, which is the default setting for English full-text search, to filter like "a", "to" such a stop word, for Chinese, we need to set this value to 1 or 2, Otherwise, most words with a length of less than 3 will be filtered out. Restart server settings token_size value 1: /etc/init.d/mysql.server restart--innodb_ft_min_token_size=1 The SHO W VARIABLES like ' innodb_ft_min_token_size ' Delete original fulltext Index:drop Index idx_full_text_content on articles; Rebuild Full-text index: CREATE fulltext index idx_content on articles (CONTENT); 3) set up local deactivation Glossary & nbsp CREATE TABLE t_cpdaily_stopwords (value VARCHAR) ENGINE = innodb; SET GLOBAL innodb_ft_server_stop word_table = ' cpdaily/t_cpdaily_stopwords '; 4) Set Configuration items show VARIABLES like ' innodb_% '; SET global innodb_optimize_fulltext_only=on; set global innodb_ft_aux_table = ' cpdb/ Articles '; OPTIMIZE TABLE articles; v. Uninstall uninstall on Mac sudo rm/usr/local/mysql sudo rm-rf/usr/local/mysql* sudo rm-rf/library/startupitems/mysqlcom sudo rm-rf /library/preferencepanes/my* Vim/etc/hostconfig (and removed the line mysqlcom=-yes-) rm-rf ~/library/preferencepanes/my* sudo rm -rf/library/receipts/mysql* sudo rm-rf/library/receipts/mysql* sudo rm-rf/var/db/receipts/ com.mysql.* uninstall on Linux 1) Yum list installed mysql* RPM-QA | Grep-i mysql 2) Yum remove MySQL mysql-devel mysql-server mysql-libs compat-mysql51   ; Rpm-aq | Grep-i mysql 3) rm-rf/var/lib/mysql 4) Whereis MySQL & nbsp rm-rf/usr/lib64/mysql rm-rf/usr/local/mysql rm-rf/usr/local/mysql/bin/mysql 5 ) Find/-name mysql rm-rf/usr/lib/mysql rm-rf/usr/share/mysql RM -rf/usr/local/mysql-5.6.35/ rm-rf/run/mysql rm–rf/usr/my.cnf Rm-rf/roOt/.mysql_sercret 6) Chkconfig--list | Grep-i mysql chkconfig--del mysqld rm-rf/var/log/mysql NOTE!!! First, install cmake: //Get the installation package and unzip wget https://cmake.org/files/v3.3/cmake-3.3.2.tar.gz Tar xzvf cmake-3.3.2.tar.gz //Enter the CMake folder to execute the boot command CD cmake-3.3.2 &nbs p; ./bootstrap //execute make gmake //Perform installation (root privileges) & nbsp; Make install II, install boost wget https://sourceforge.Net/projects/boost/ files/boost/1.62.0/boost_1_62_0.tar.gz TAR-ZXVF boost_1_62_0.tar.gz Cp-r boost_1_62_ 0/usr/local/boost
MySQL installation configuration, commands, exception minutes
