mysql

mysql intro

What is mysql? mysql is an open source relational database management system

Why should I should it?

You shouldn’t : For performance and license reason.

Why will I use it?

Because it is very popular among website developpers.

mysql install

Get the port

cd /usr/ports/databases/mysql51-server
make install clean

Initialize the database

mkdir /home/db/mysql/data
chown mysql /home/db/mysql/data
/usr/local/bin/mysql_install_db --user=mysql --datadir=/home/db/mysql/data
/usr/local/bin/mysqld_safe --skip-networking --user=mysql --datadir=/home/db/mysql/data &

Secure the database

/usr/local/bin/mysqladmin --user=root password 'newpassword'
mysql -u root -p
drop database test;
use mysql;
delete from db;
delete from user where not (host="localhost" and user="root");
flush privileges;
(type control D)
/usr/local/bin/mysqladmin -u root -p shutdown

Edit /etc/rc.conf

mysql_enable="YES"
mysql_dbdir="/home/db/mysql/data"
mysql_pidfile="/var/run/mysqld/mysqld.pid"

Add a few directories

mkdir /var/run/mysqld
mkdir /home/log/mysqld
chmod 700 /var/run/mysqld
chmod 700 /home/log/mysqld

And a configuration file (create /usr/local/etc/my.cnf)

# The MySQL database server configuration file.
[client]
port                    = 3306
socket                  = /home/db/mysql/data/mysqld.sock
[mysqld_safe]
socket                  = /home/db/mysql/data/mysqld.sock
nice                    = 0
[mysqld]
user                    = mysql
pid-file                = /var/run/mysqld/mysqld.pid
socket                  = /home/db/mysql/data/mysqld.sock
basedir                 = /home/local
datadir                 = /home/db/mysql/data
tmpdir                  = /tmp
language                = /usr/local/share/mysql/english
bind-address            = 127.0.0.1
#tuning
key_buffer_size         = 16M
max_allowed_packet      = 1M
table_open_cache        = 64
sort_buffer_size        = 512K
net_buffer_length       = 8K
read_buffer_size        = 256K
read_rnd_buffer_size    = 512K
myisam_sort_buffer_size = 8M
query_cache_limit       = 1M
query_cache_size        = 32M
# Logging and Replication
log_slow_queries        = /home/log/mysqld/mysql-slow.log
long_query_time         = 1
log-bin                 = /home/log/mysqld/mysql-bin
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_format           = mixed
server-id               = 1
[mysqldump]
quick
quote-names
max_allowed_packet      = 16M
[isamchk]
key_buffer              = 16M

Add mysql root password to ~/.my.cnf

[client]
user = root
password = c5654sdakfl
host = 127.0.0.1

mysql tips

~/.my.cnf

[client]
user = root
password = 12345
host = 127.0.0.1

Purge bin logs

PURGE BINARY LOGS BEFORE '2019-12-01 00:06:00';

List databases

mysql -e 'show databases\G' | grep Database | awk '{print $2}'

Repair a table

repair table name_of_my_table;

Save a databse

mysqldump -Q --opt --routines --triggers -B name_of_my_base |gzip > name_of_my_base.sql.gz

Add a database and a user

CREATE DATABASE name_of_my_base CHARACTER SET utf8 COLLATE utf8_general_ci;
grant all privileges on name_of_my_base.* to 'utilisateur'@'%' identified by 'hackmeagain';

If you want the db case sensitive

CREATE DATABASE name_of_my_base CHARACTER SET utf8 COLLATE utf8_bin;

Update text in all records for a given column

update matable SET my_column=(REPLACE(my_column,'old_text','new_text'));

Add a simple index

CREATE INDEX idx_colname ON table (colname);

Substract a table to antoher

SELECT my_column_1 FROM my_table_1 LEFT JOIN my_table_2 ON my_column_1=my_column_2 WHERE my_column_2 IS NULL;

Solving load problems

mysqlsla --log-type slow /home/log/mysqld/mysql-slow.log

Partition a table

ALTER TABLE matable  PARTITION BY RANGE(id) (
PARTITION p0 VALUES LESS THAN (100000),
PARTITION p1 VALUES LESS THAN (200000),
PARTITION p2 VALUES LESS THAN MAXVALUE);

Change wordpress password

update wp_users set user_pass=MD5('dontforgetthispasswdagain') where user_login='luser';

Feedback and comments are welcome on this page .