How to install MySQ Server 6.0 in Ubuntu 10.04 LTS Desktop?
(All commands need root user privilege of Ubuntu)
1. Download "mysql-6.0.11-alpha-linux-i686-glibc23.tar.gz" from MySQL website.
2. tar -zxvf mysql-6.0.11-alpha-linux-i686-glibc23.tar.gz
3. Rename it: mv mysql-6.0.11-alpha-linux-i686-glibc23.tar.gz mysql
4. Copy mysql folder to "/usr/local/mysql": cp -r mysql /usr/local/mysql
5. Change mode of mysql: chmod -R 777 mysql
6. Copy "my-small.cnf" to "/etc/my.cnf":
# cd /usr/local/mysql/support-files
# cp my-small.cnf /etc/my.cnf
7. Configure my.cnf (Red font text is updated; Green is added):
# Example MySQL config file for small systems.
#
# This is for a system with little memory (<= 64M) where MySQL is only used
# from time to time and it's important that the mysqld daemon
# doesn't use much resources.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/mysql/data) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
[color=red]socket = /tmp/mysql.sock[/color] [color=gray] (Update it to this sock file if its position is changed.
Actually you need find this file - find / -name mysql.sock, and put down its name here.)[/color]
No bind-address to have remote client (MySQL-Front) access this MySQL server
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
[color=red]user=root [color=gray][size=xx-small]Add root user[/size][/color])
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data[/color]
port = 3306
[color=red]socket = /tmp/mysql.sock [/color] [color=gray]Update it to this sock file if its position is changed.)[/color]
skip-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (using the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
server-id = 1
# Uncomment the following if you want to log updates
#log-bin=mysql-bin
# binary logging format - mixed recommended
#binlog_format=mixed
# Uncomment the following if you are using InnoDB tables
[color=red]innodb_data_home_dir = /usr/local/mysql/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data/[/color]
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
[color=red]innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1[/color]
#innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
[mysqlhotcopy]
interactive-timeout
8. Go to "/usr/mysql/" and run "scripts/mysql_install_db", NOTE: If you go to scripts and run "mysql_install_db", you'll get a message that this command is in "mysql-5.1-server, mysql-5.1-core-server and mysql-5.1-client package". So if you go to scripts folder, you have to type "./mysql_install_db". This will avoid confusion of your Ubuntu:
# cd /usr/mysql/scripts/
# ./mysql_install_db
By this way, maybe you won't set root password successfully, just try the following approaches:
Approach 1:
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER! To do so, start the server, then issue the following commands:
/usr/local/mysql/bin/mysqladmin -u root password 'new-password'
/usr/local/mysql/bin/mysqladmin -u root -h xavier-desktop (your machine name or localhost) password 'new-password'
Approach 2 (Even though you've run the "mysql_install_db" command):
Alternatively you can run:
/usr/local/mysql/bin/mysql_secure_installation
To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system. Definitely some variables need updating. Here I don't recommend you to have your mysqld started at boot time. Later, I will tell you how to start and stop MySQL Server.
How to STARTUP and SHUTDOWN mysql server 6.0
SHUTDOWN: # /usr/local/mysql/bin/mysqladmin -uroot -p shutdown
-u username
-p password
-h hostname
-P port
[color=red]If you still cannot shutdown it, just kill the process.[/color]
# ps -aux|grep mysql
# kill -9 MySQL's PID
[color=red]STARTUP: # /usr/local/mysql/bin/mysqld[/color]
FAQ of MYSQL 6.0 in UBUNTU 10.04 LTS DESKTOP
1. How to login MySQL
# /usr/local/mysql/bin/mysql -uroot -p (database)
2. How to change user's password
Approach 1:
mysql>use mysql
mysql>update user set password=password(“xavier”) where user=”root”;
mysql>flush privileges;
Approach 2:
Format: mysqladmin -uUSERNAME -pOLD_PASSWORD password NEW_PASSWORD
#mysqladmin -uroot -password xavier
Note: If password of root is empty, -p OLD_PASSWORD can be neglected.
3. How to connect MySQL through TCP, not local sock
In your machine, make sure you have install MySQL Client. When you trying to connect Remote MySQL Server(Make sure that you have start up your MySQL in your machine):
mysql -h remote-MySQL-Server-IP -u remote-MySQL-Server-IP-Username -p (This command can be typed in Win CMD or Linux Terminal Console)
4. How to Connect MySQL with MyEclipse?
Download MySQL Connector/J from MySQL website – format is tar.gz, unzip it and configure DB Explorer in MyEclipse by using this jar.
[color=red]表名不区分大小写
1.用ROOT登录,修改/etc/my.cnf
2.在[mysqld]下加入一行:lower_case_table_names=1[/color]
(All commands need root user privilege of Ubuntu)
1. Download "mysql-6.0.11-alpha-linux-i686-glibc23.tar.gz" from MySQL website.
2. tar -zxvf mysql-6.0.11-alpha-linux-i686-glibc23.tar.gz
3. Rename it: mv mysql-6.0.11-alpha-linux-i686-glibc23.tar.gz mysql
4. Copy mysql folder to "/usr/local/mysql": cp -r mysql /usr/local/mysql
5. Change mode of mysql: chmod -R 777 mysql
6. Copy "my-small.cnf" to "/etc/my.cnf":
# cd /usr/local/mysql/support-files
# cp my-small.cnf /etc/my.cnf
7. Configure my.cnf (Red font text is updated; Green is added):
# Example MySQL config file for small systems.
#
# This is for a system with little memory (<= 64M) where MySQL is only used
# from time to time and it's important that the mysqld daemon
# doesn't use much resources.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/mysql/data) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
[color=red]socket = /tmp/mysql.sock[/color] [color=gray] (Update it to this sock file if its position is changed.
Actually you need find this file - find / -name mysql.sock, and put down its name here.)[/color]
No bind-address to have remote client (MySQL-Front) access this MySQL server
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
[color=red]user=root [color=gray][size=xx-small]Add root user[/size][/color])
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data[/color]
port = 3306
[color=red]socket = /tmp/mysql.sock [/color] [color=gray]Update it to this sock file if its position is changed.)[/color]
skip-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (using the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
server-id = 1
# Uncomment the following if you want to log updates
#log-bin=mysql-bin
# binary logging format - mixed recommended
#binlog_format=mixed
# Uncomment the following if you are using InnoDB tables
[color=red]innodb_data_home_dir = /usr/local/mysql/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data/[/color]
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
[color=red]innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1[/color]
#innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
[mysqlhotcopy]
interactive-timeout
8. Go to "/usr/mysql/" and run "scripts/mysql_install_db", NOTE: If you go to scripts and run "mysql_install_db", you'll get a message that this command is in "mysql-5.1-server, mysql-5.1-core-server and mysql-5.1-client package". So if you go to scripts folder, you have to type "./mysql_install_db". This will avoid confusion of your Ubuntu:
# cd /usr/mysql/scripts/
# ./mysql_install_db
By this way, maybe you won't set root password successfully, just try the following approaches:
Approach 1:
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER! To do so, start the server, then issue the following commands:
/usr/local/mysql/bin/mysqladmin -u root password 'new-password'
/usr/local/mysql/bin/mysqladmin -u root -h xavier-desktop (your machine name or localhost) password 'new-password'
Approach 2 (Even though you've run the "mysql_install_db" command):
Alternatively you can run:
/usr/local/mysql/bin/mysql_secure_installation
To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system. Definitely some variables need updating. Here I don't recommend you to have your mysqld started at boot time. Later, I will tell you how to start and stop MySQL Server.
How to STARTUP and SHUTDOWN mysql server 6.0
SHUTDOWN: # /usr/local/mysql/bin/mysqladmin -uroot -p shutdown
-u username
-p password
-h hostname
-P port
[color=red]If you still cannot shutdown it, just kill the process.[/color]
# ps -aux|grep mysql
# kill -9 MySQL's PID
[color=red]STARTUP: # /usr/local/mysql/bin/mysqld[/color]
FAQ of MYSQL 6.0 in UBUNTU 10.04 LTS DESKTOP
1. How to login MySQL
# /usr/local/mysql/bin/mysql -uroot -p (database)
2. How to change user's password
Approach 1:
mysql>use mysql
mysql>update user set password=password(“xavier”) where user=”root”;
mysql>flush privileges;
Approach 2:
Format: mysqladmin -uUSERNAME -pOLD_PASSWORD password NEW_PASSWORD
#mysqladmin -uroot -password xavier
Note: If password of root is empty, -p OLD_PASSWORD can be neglected.
3. How to connect MySQL through TCP, not local sock
In your machine, make sure you have install MySQL Client. When you trying to connect Remote MySQL Server(Make sure that you have start up your MySQL in your machine):
mysql -h remote-MySQL-Server-IP -u remote-MySQL-Server-IP-Username -p (This command can be typed in Win CMD or Linux Terminal Console)
4. How to Connect MySQL with MyEclipse?
Download MySQL Connector/J from MySQL website – format is tar.gz, unzip it and configure DB Explorer in MyEclipse by using this jar.
[color=red]表名不区分大小写
1.用ROOT登录,修改/etc/my.cnf
2.在[mysqld]下加入一行:lower_case_table_names=1[/color]