For more information about how to install and configure Mysql in Ubuntu, see ubuntu installation and configuration.

For more information about how to install and configure Mysql in Ubuntu, see ubuntu installation and configuration.

There are three installation methods for installing Mysq In Ubuntu. The following describes the installation methods. The specific content is as follows:

1. Install from the InternetSudo apt-get install mysql-server. After the environment variables have been automatically configured, you can directly use the mysql command.

Note:We recommend that you change cn in/etc/apt/source. list to us, and the servers in the United States are much faster than those in China.

2. Install the offline packageTake mysql-5.0.45-linux-i686-icc-glibc23.tar.gz as an example.

3. Binary Package installation:After installation is complete, environment variables are automatically configured. You can directly use the mysql command.

Online installation and Binary Package installation are relatively simple, with emphasis on the installation of offline packages.

1. groupadd mysql

2. mkdir/home/mysql

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

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

5. Unzip the 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.

10. scripts/mysql_install_db -- user = mysql (Be sure to run the command in the mysql directory. Note that the output text contains the command to change the root password and start mysql)

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

Next, configure and manage msyql:

1. Modify the maximum number of mysql connections:Cp support-files/my-medium.cnf./my. cnf, vim my. cnf, add or modify max_connections = 1024

For my. cnf: mysql, search for my. cnf:/etc, mysql installation directory, and data under installation directory. /Etc is set globally.

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

View mysql version: mysqladmin-u root-p version

Note:For online installation or binary installation, run 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 and p

4. Set mysql to start automatically:Add the startup command to the/etc/rc. local file.

5. Allow remote root login:

　1) log on to mysql on the local machine:Mysql-u root-p (-p must be available); change the database: use mysql;

　2) from all hosts:Grant all privileges 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) Go to the mysql database to check whether the data with host % is added: use mysql; select * from user;

6. Create a database and create a user:

　1) database creation:Create database test1;

　2) create users and grant permissions:Grant all privileges on test1. * to user_test @ "%" identified by "passw0rd" with grant option;

　3) delete a database:Drop database test1;

7. Delete permission:

1) revoke all privileges on test1. * from test1 @ "% ";

2) use mysql;

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

4) flush privileges;

8. display all databases:Show databases; show all tables in the database: show tables;

9. remotely log on to 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 and 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 my. cnf under/etc can make the client settings take effect. The settings under the installation directory can only make the server settings valid.

Binary installation modification/etc/mysql/my. cnf

11. upgrade old data to utf8 (using latin1 as an example ):

1) Export old data: mysqldump -- default-character-set = latin1-hlocalhost-uroot-B dbname -- tables old_table> old. SQL

2) Conversion encoding (Linux and UNIX): iconv-t UTF-8-f gb2312-c old. SQL> new. SQL

Assume that the data in the original table is gb2312. You can also remove-f to enable iconv to automatically determine the original character set.

3) Import: Modify new. SQL, add a sentence "SET NAMES utf8;" before the insert or modify statement, modify all gb2312 to utf8, and save the statement.

Mysql-hlocalhost-uroot-p dbname <new. SQL

If the error "max_allowed_packet" is reported, it is because the file is too large. The default mysql parameter is 1 MB. Modify the value in my. cnf (mysql needs to be restarted ).

12. Clients supporting utf8:Mysql-Front, Navicat, PhpMyAdmin, and Linux Shell (run set names utf8 after connection; then you can read and write utf8 data. 10.4 you don't need to execute this statement after the setting is complete)

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

Recover Database: mysql-uroot-p <name. SQL

Recovery table: mysql-uroot-p dbname <name. SQL (the database must be specified)

14. Copy

Mysql supports one-way asynchronous replication, that is, one server is the master server, and one or more other servers are the slave server. Replication is implemented through binary logs. The master server writes data and reads data from the server. Multiple master servers can be implemented, but problems that are not encountered by a single server are not recommended ).

1) create a user dedicated to replication on the master server: grant replication slave on *. * to 'replicationuser' @ '192. 168.0.87 'identified by 'iverson ';

2 ). refresh all the tables and block write statements on the MASTER server: flush tables with read lock; then read the binary file name and branch on the MASTER server: show master status; record the values of File and Position. Close the master server after logging: mysqladmin-uroot-ppassw0rd shutdown

If the output is empty, binary logs are not enabled on the server. In the my. cnf file, add log-bin = mysql-bin under [mysqld], which is displayed after restart.

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

You need to create a snapshot for the database to be copied on the master server. For Windows, you can use the zip format. For Linux and Unix, it is best to use the tar command. Then upload the data to the mysql data directory on the slave server and decompress the package.

Cd mysql-data-dir

Tar cvzf mysql-snapshot.tar./mydb

Note:The Snapshot should not contain any log file or *. info file, but only the data file (*. frm and *. opt) of the database to be copied.

You can use mysqldump to recover data from the slave server to ensure data consistency.

4) Confirm that the [mysqld] section of the my. cnf file on the master server contains the log-bin option and server-id, and start the master server:

[mysqld]　　　log-bin=mysql-bin　　　server-id=1

5) Stop the slave server, add the server-id, and then start the slave server:

[Mysqld]

Server-id = 2

Note:The server-id here is the id of the slave server, which must be different from the master server and other slave servers.

You can add the read-only option to the configuration file of the server. In this way, the slave server only accepts the SQL statements of the self-owned server to ensure that the data is not modified by other methods.

6). Execute the following statement on the slave server and replace the option with the system actual value:

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 slave thread: mysql> START slave; stop SLAVE thread: stop slave; (Note: The firewall of the master server should allow port 3306 to be connected)

Verification:At this time, the data on the master server and the slave server should be consistent. The data inserted, modified, and deleted on the master server will be updated to the slave server, and the table creation and deletion will be the same.

The above is the detailed steps for installing and configuring Mysql In Ubuntu. I hope it will be helpful for your learning.