Graphics and text detailed Ubuntu installation configuration Mysql Tutorial _mysql

Ubuntu installation MYSQ has three kinds of installation methods, below for everyone to explain, the specific content as follows

1. Install sudo apt-get install Mysql-server from the Internet. After loading the environment variable, you can use the MySQL command directly.

Note: It is recommended to change the CN in/etc/apt/source.list to us, the server in the United States is much faster than that in China.

2. Install off-line package , take mysql-5.0.45-linux-i686-icc-glibc23.tar.gz for example.

3. Binary Package Installation: installation completed has been automatically configured environment variables, you can directly use the MySQL command

Online installation and binary package installation is relatively simple, focusing on the installation of offline packages.

1. Groupadd MySQL

2. Mkdir/home/mysql

3. useradd-g mysql-d/home/mysql MySQL

4. Copy mysql-5.0.45-linux-i686-icc-glibc23.tar.gz to/usr/local directory

5. Decompression: Tar zxvf mysql-5.0.45-linux-i686-icc-glibc23.tar.gz

6. Ln-s mysql-5.0.45-linux-i686-icc-glibc23 MySQL

7. Cd/usr/local/mysql

8. chown-r MySQL.

9. chgrp-r MySQL.

scripts/mysql_install_db--user=mysql (must be executed in the MySQL directory, pay attention to the output of the text, there are changes to the root password and the command to start MySQL)

11. Set password for root:./bin/mysqladmin-u root password ' passw0rd '

Next, configure and manage MSYQL:

1. Modify MySQL Maximum connection number:cp support-files/my-medium.cnf./my.cnf,vim MY.CNF, add or modify max_connections=1024

About My.cnf:mysql in the following order search My.cnf:/etc,mysql installation directory, installation directory of data. /ETC is the global setting.

2. Start MySQL:/usr/local/mysql/bin/mysqld_safe--user=mysql &

View MySQL version: Mysqladmin-u root-p

Note: online installation or binary installation can be used directly using the following command to start and stop MySQL:/etc/init.d/mysql start|stop|restart

3. Stop MySQL:mysqladmin-uroot-ppassw0rd shutdown Note that there is no space after u,p

4. Set MySQL from start: Add the launch command to the/etc/rc.local file

5. Allow root Remote login:

　1 Local login mysql:mysql-u root-p (P must have); Change database: Use MySQL;

　2 from all hosts: Grant allprivileges on *.* to root@ '% ' identified by ' passw0rd ' with GRANT option;

　3 from the specified host:grant all privileges on *.* to root@ "192.168.11.205" identified by "Passw0rd" with GRANT option; Flush privileges;

4 into the MySQL library to see if the host is% of the data added: use MySQL; select * from user;

6. Create a database, create User:

　1) Build database:CREATE DATABASE test1;

　2 Build the user, empower:grant all privileges on test1.* to user_test@ "%" identified by "Passw0rd" with GRANT option;

　3 Delete database: Drop dbtest1;

7. Delete Permission:

1) Revoke all privileges to test1.* from test1@ "%";

2 use MySQL;

3) Delete from user where user= "root" and host= "%";

4) flush privileges;

8. Display all databases: showdatabases; Display all tables in the library: show tables;

9. telnet MySQL:mysql-h ip-u user-p

10. Set the character set (take UTF8 as an example):

1 View the current encoding: show variables like ' character% ';

2) Modify MY.CNF, add Default-character-set=utf8 under [client]

3 Add default-character-set=utf8,init_connect= ' Set NAMES UTF8 under [Server];

4) Restart MySQL.

Note: only modify the my.cnf under/etc to make client settings work, settings under the installation directory can only make the server's settings valid.

Binary installation of the modified/ETC/MYSQL/MY.CNF can be

11. Old data is upgraded to UTF8 (old data in latin1 case):

1) Exporting old data: mysqldump--default-character-set=latin1-hlocalhost-uroot-b dbname--tables old_table >old.sql

2 conversion Coding (Linux and Unix): Iconv-t utf-8-F gb2312-c old.sql > New.sql

This assumes that the data for the original table is gb2312, or that the-F can be removed, allowing the iconv to automatically determine the original character set.

3) Import: Modify the New.sql, add a sentence before inserting or modifying the statement: "SET NAMES UTF8;" and modify all gb2312 for UTF8, save.

　　Mysql-hlocalhost-uroot-p dbname < New.sql

If reported max_allowed_packet error, because the file is too large, mysql default this parameter is 1M, modify the value in MY.CNF (need to restart MySQL).

12. Clients that support UTF8:mysql-front,navicat,phpmyadmin,linux Shell (after the connection executes set NAMES UTF8, you can read and write UTF8 data. 10.4 After setting up, you don't have to do this again.

13. Backup and Recovery

Back up a single database: mysqldump-uroot-p-B dbname > Dbname.sql

Back up all databases: Mysqldump-uroot-p--all-databases > All.sql

Backup table: mysqldump-uroot-p-B dbname--table tablename > Tablename.sql

Recovery database: Mysql-uroot-p < Name.sql

Recovery table: Mysql-uroot-p dbname < Name.sql (database must be specified)

14. Copy

MySQL supports one-way asynchronous replication, that is, one server is the master server, the other one or more servers to do from the server. Replication is implemented through binary logs, which are written by the primary server and read from the server. You can implement multiple master servers, but you will encounter problems not encountered by a single server (not recommended).

1. Create a user dedicated to replication on the primary server: Grant replication slave on *.* to ' replicationuser ' @ ' 192.168.0.87 ' identified by ' Iverson ';

2. Refresh all table and block write statements on the primary server: Flush tables with read lock; Then read the binary binary file name and branch on the primary server: Show Master STATUS; record the file and position values. Shut down the primary server after logging: mysqladmin-uroot-ppassw0rd shutdown

If the output is empty, the server does not enable binary logging, add log-bin=mysql-bin under [Mysqld] in the my.cnf file, and then reboot.

3. Create a snapshot for the primary server (snapshot)

You need to make a snapshot of a database that needs to be replicated on the primary server, Windows can use the ZIP format, and Linux and UNIX are best to use the tar command. Then upload the data directory from the server MySQL and unzip it.

CD Mysql-data-dir

Tar cvzf Mysql-snapshot.tar./mydb

Note: you should not include any log files or *.info files in the snapshot, only the data files (*.frm and *.opt) files for the databases you want to replicate.

Database backup (mysqldump) can be used for data recovery from the server to ensure data consistency.

4. Verify that the [Mysqld]section contains Log-bin options and Server-id for the my.cnf file on the primary server and start the primary server:

[Mysqld]

Log-bin=mysql-bin

server-id=1

5. Stop from server, join Server-id, and then start from server:

[Mysqld]

server-id=2

Note: here the Server-id is from the server ID and must be different from the primary server and the other from the server.

You can add the READ-ONLY option from the server's configuration file so that only SQL from the primary server is accepted from the server, ensuring that the data is not modified by other means.

6. Execute the following statement from the server, replacing the option with the actual value of the system:

Change Master to master_host= ' Master_host ', master_user= ' replication_user ', master_password= ' replication_pwd

', Master_log_file= ' Recorded_log_file_name ', master_log_pos=log_position;

7. Start from thread:mysql> start SLAVE; Stop from thread: Stop slave; (note: The firewall of the primary server should allow 3306 port connections)

Authentication: At this time the primary server and the data from the server should be consistent, the main server inserts modify delete data will be updated from the server, the table, delete table, etc. is the same.

Wonderful topic sharing: MySQL different versions of the installation Tutorials mysql5.7 version Installation Tutorials

The above is installed in Ubuntu installation of MySQL detailed steps, I hope to help you learn.