MySQL installation configuration, commands, exception minutes

Source: Internet
Author: User

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 &nbsp ;    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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.