1. MySQL Three installation methods:
1 ) Install from the Internet
sudo apt-get install mysql-server
Note: It is recommended to change the cn in /etc/apt/source.list to us. The server in the US is much faster than in China. The modification command is as follows: sudo sed -i "s/cn/us/g" sources.list
2 ) Install offline packages
Take mysql-5.5.16-linux2.6-x86_64.tar.gz as an example, mysql official website
3 ) Binary package installation
After the installation is complete, the environment variables have been automatically configured, you can use the mysql command directly
Online installation (1) and binary package installation (3) are relatively simple, focusing on installing offline packages (2):
groupadd mysql
mkdir /home/mysql
useradd -g mysql -d /home/mysql mysql
copy mysql-5.0.45-linux-i686-icc-glibc23.tar.gz to the /usr/local directory
Unzip: tar zxvf mysql-5.5.16-linux2.6-x86_64.tar.gz
ln -s mysql-5.0.45-linux-i686-icc-glibc23 mysql
cd /usr/local/mysql
chown -R mysql .
chgrp -R mysql .
scripts/mysql_install_db --user=mysql (must be executed in the mysql directory, pay attention to the output text, there are commands to modify the root password and start mysql)
Set a password for root: ./bin/mysqladmin -u root password'passw0rd'
2. Configure and manage msyql:
About my.cnf: mysql searches for my.cnf:/etc, mysql installation directory, and data in the installation directory in the following order. Under /etc are global settings.
Note: For online installation or binary installation, you can directly use the following commands to start and stop mysql: /etc/init.d/mysql start | stop | restart
Stop mysql: mysqladmin -uroot -ppassw0rd shutdown (note that there is no space after u, p)
Set mysql to start automatically: add the startup command to the /etc/rc.local file
5. Allow root remote login:
Log in to mysql on this machine: mysql -u root -p (-p must have); change the database: use mysql;
From all hosts: grant all privileges on . to root@"%" identified by "password" with grant option; flush privileges;
From the specified host: grant all privileges on . to root@"192.168.11.205" identified by "password" with grant option; flush privileges;
Enter the mysql library to check whether the data whose host is% is added: use mysql; select * from user;
Read-only dump database: mysqldump -h 172.192.1.12 -uroot -p123456 --single-transaction your_db> your_db_bk.sql
Connect to mysql remotely through the MySQL port: mysql -h 122.128.10.114 -P 31206 -uroot -pyg123456 // -P mysql port configured in the /etc/mysql/my.cnf configuration file, -p password
6. Create database, create user:
Build a database: create database test1;
Create user, grant authorization: grant all privileges on test1.* to user_test@"%" identified by "password" with grant option;
Delete the 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;
Show all databases: show databases; Show all tables in the database: show tables;
Remote login to mysql: mysql -h ip -u user -p
10. Set the character set (take utf8 as an example):
View the current encoding: show variables like'character%';
Modify my.cnf, add default-character-set=utf8 under [client]
Add default-character-set=utf8, init_connect='SET NAMES utf8;' under [server]
Restart mysql.
Note: Only modify the my.cnf under /etc to make the client settings effective, and the settings under the installation directory can only make the server settings effective. Modify /etc/mysql/my.cnf for binary installation
11. Upgrade the old data to utf8 (the old data is latin1 as an example):
Export old data: mysqldump --default-character-set=latin1 -hlocalhost -uroot -B dbname --tables old_table> old.sql
Conversion encoding (Linux and UNIX): iconv -t utf-8 -f gb2312 -c old.sql> new.sql Here it is assumed that the data in the original table is gb2312. You can also remove -f to let iconv automatically determine the original character set.
Import: modify new.sql, add a sentence before the insert or modify statement: "SET NAMES utf8;", and modify all gb2312 to utf8, save it.
mysql -hlocalhost -uroot -p dbname < new.sql
If an error of max_allowed_packet is reported, it is because the file is too large. The default parameter of mysql is 1M. You can modify the value in my.cnf (you need to restart mysql).
12. Clients that support utf8:
Mysql-Front, Navicat, PhpMyAdmin, Linux Shell (After connecting, execute SET NAMES utf8; then you can read and write utf8 data. After 10.4 is set, there is no need to execute this sentence)
13. Backup and restore
Back up a single database: mysqldump -u root -p -B dbname> dbname.sql
Back up all databases: mysqldump -u root -p --all-databases> all.sql
Backup table: mysqldump -u root -p -B dbname --table tablename> tablename.sql
Restore the database: mysql -u root -p name <name.sql
Recovery table: mysql -u root -p dbname <name.sql (The database must be specified, but the table may not be specified, because the table must be placed in the specified database, haha)
14. copy
Mysql supports one-way asynchronous replication, that is, one server serves as the master server, and one or more other servers serve as slave servers. Replication is achieved through binary logs, where the master server writes and the slave server reads. Multiple master servers can be implemented, but you will encounter problems that a single server has not encountered before (not recommended).
1). Create a user dedicated for replication on the primary server: grant replication slave on . to 'replicationuser'@'192.168.0.87' identified by'iverson';
2). Flush all tables and block write statements on the main server: flush tables with read lock; Then read the binary binary file name and branch on the main server: SHOW MASTER STATUS; Record the values of File and Position. Shut down the main server after recording: mysqladmin -uroot -ppassw0rd shutdown
If the output is blank, it means that the server has not enabled binary logging. Add log-bin=mysql-bin under [mysqld] in the my.cnf file, and it will be available after restarting.
3). Create a snapshot for the main server (snapshot)
You need to create a snapshot of the database that needs to be copied on the master server. Windows can use the zip format, and Linux and Unix are best to use the tar command. Then upload it to the data directory of the slave server mysql and unzip it.
cd mysql-data-dir
tar cvzf mysql-snapshot.tar ./mydb
Note: The snapshot should not contain any log files or .info files, only the data files (.frm and *.opt) files of the database to be copied.
You can use database backup (mysqldump) to restore data from the server to ensure data consistency.
4). Confirm that the [mysqld] section of the my.cnf file on the main server contains the log-bin option and server-id, and start the main server:
[mysqld]
log-bin=mysql-bin
server-id=1
5). Stop the slave server, add 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 a read-only option to the configuration file of the slave server, so that the slave server only accepts SQL from the master server, ensuring that the data will not be modified by other means.
6). Execute the following statement on the slave server, replacing the options with the system real values:
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 the slave thread: mysql> START SLAVE; Stop the slave thread: stop slave; (Note: the firewall of the master server should allow port 3306 to connect)
Verification: At this time, the data on the master server and the slave server should be the same. Inserting, modifying, deleting data on the master server will be updated to the slave server, creating tables, deleting tables, etc. are the same.
**Commonly used commands: **
1 ) View version
mysqladmin -u root -p version
2 ) Mysql connection failed
Exception: ERROR 2003 (HY000): Can't connect to MySQL server
solve:
1. Questions raised
/usr/local/webserver/mysql/bin/mysql -u root -h 172.29.141.112 -p -S /tmp/mysql.sock
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '172.29.141.112' (113)
2. Problem analysis
The above problems may have the following possibilities
**1.**Skip-networking is configured in the my.cnf configuration file
The skip-networking parameter causes all TCP/IP ports to not be monitored, which means that no other clients can connect to this mysql server using the network if it is out of the local machine
So you need to comment out this parameter.
**2.**Parameter configuration of bindaddress in the my.cnf configuration file
bindaddress, and some are bind-address. This parameter specifies which ip addresses are configured so that the mysql server only responds to requests for which ip addresses, so this parameter needs to be commented out.
**3.**The reason for the firewall
Turn off the firewall through /etc/init.d/iptables stop
My problem is caused by this reason. It can be used by closing the firewall of the mysql server.
Three. Solving the problem
**1.**If it is the first reason above, find my.cnf and comment out the skip-networking parameter
sed -i 's%skip-networking%#skip-networking%g' my.cnf
**2.**If it is the second reason above, then find my.cnf and comment out the bind-address parameter
sed -i 's%bind-address%#bind-address%g' my.cnf
sed -i 's%bindaddress%#bindaddress%g' my.cnf
It is best to check this parameter after modifying it.
**3.**If it is the third reason above, then turn off the firewall or configure accordingly
/etc/init.d/iptables stop
**Additional: **
1 ) View the process being processed:
show processlist;
2 ) View the space occupied by the database:
show table status from some_database;
For example: show table status from top_500; # top_500 is a database
SELECT table_schema top_500, sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;
The query results are as follows:
Reference recommendation:
Ubuntu installation and configuration Mysql
MySQL command operation (Linux platform)
Install MySQL 5.5.25 under Ubuntu 12.04
Recommended Posts