MySql常用命令

mysql manual

install mysql for ubuntu

root@ubuntu:~# sudo apt-get install mysql-server
root@ubuntu:~# apt install mysql-client 
root@ubuntu:~# apt install libmysqlclient-dev 

# arch linux
yaourt -S mariadb

# systemctl start mariadb
# mysql_secure_installation
# systemctl restart mariadb

post install

# arch
    mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql
# both
    mysql_secure_installation

initial

check mysql installation

sudo netstat -tap | grep mysql

mysql change ip addr

root@ubuntu:~# vi /etc/mysql/mysql.conf.d/mysqld.cnf
#bind-address = 127.0.0.1

1. connect

mysql -u root -p
use mysql

# delete anonymous user
select user, host from user;
delete from user where host='localhost' and user='';

1. unicode support
   server side

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

client side

default-character-set = utf8

remote connect

1. edit my.cnf
   in [mysqld]

bind-address=192.168.1.100
# skip-networking

1. restart mysql

login to mysql and create database with user 3. grant access to remote IP address

create database ros;
grant all on database_name.* to username@'%' identified by 'password';
grant all on cityrobot.* to ros@'%' identified by 'ros';
grant all on ros.* to ros@'%' identified by 'Roos123@';
-- or
grant all on foo.* to bar@'remote_ip' identified by 'password' with grant option;

remote_ip is the address where the client is on

grant access to an existing database

update db set Host='remote_ip' where Db='webdb';
update user set Host='remote_ip' where user='webadmin';

创建用户后flush privileges; then restart mysql

flush privileges;

/etc/init.d/mysql restart

1. test it

mysql -u webadmin -h server_ip -p

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql -uroot -p
mysql> uninstall plugin validate_password;