Mysql Proxy Lua successfully installed in Ubuntu 9.04

Ubuntu 9.04InstallMysql Proxy LuaIs the content to be introduced in this article, mainly to understandMysql ProxyInUbuntu 9.04The installation environment under and has passed the test. For details, see this article.

Install mysql

1,

 
 

  
  
apt-get install mysql-server mysql-client libmysqlclient15-dev 
 
 

2. Enter

 
 

  
  
mysql  mysql -uroot -p 
 
 

Password root

3. Reset the mysql user root password.

 
 

  
  
GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY "123456"; 
 
 

4. Create a new database proxy

 
 

  
  
create database proxy;   
 
 

5. create user test with full operation permissions on the proxy Database

 
 

  
  
grant all privileges on proxy.* to test@localhost identified by "zhaogongyu"; 
 
 

6. Allow the test user to log on to MySQL from any machine.

 
 

  
  
Step 1: grant all privileges on proxy. * to test @ "%" identified by "zhaogongyu ";
  
  
 
  
  
Step 2: $ sudo gedit/etc/mysql/my. cnf
 
 

Old Version

 
 

  
  
skip-networking => #skip-networking 
 
 

New Version

 
 

  
  
Bind-address = 127.0.0.1 => bind-address = IP address of your machine
 
 

This allows other machines to access MySQL.

7. Restart the mysql Command

 
 

  
  
sudo /etc/init.d/mysql restart 
 
 

8. Start mysql at startup

 
 

  
  
vim /etc/rc.local 
 
 

Add the following row to the last row.

 
 

  
  
cd /usr/bin; /usr/bin/safe_mysqld --user=root &  
  
  
exit 0 
 
 

Note: Remember to add cd/usr/local/mysql-5.1.36; and add a space after the sentence, otherwise it will cause the failure to automatically start

9. export data from the database

 
 

  
  
mysqldump -uroot -p --default-character-set=latin1 --set-charset=gbk --skip-opt mydatabase > d.sql 
 
 

10. Create a new database

 
 

  
  
create database 'newdata' default character set gbk collate gbk_chinese_ci; 
 
 

11. Import Database

 
 

  
  
mysql -uroot -p --default-character=gbk -f newmydatabase >source d.sql ..>exit 
 
 

Install read/write splitting mysql-proxy

I. mysql

 
 

  
  
1. apt-get install mysql-server mysql-client libmysqlclient15-dev
  
  
 
  
  
2. Go to mysql-uroot-p
 
 

Password root

3. Reset the mysql user root password.

 
 

  
  
GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY "123456"; 
 
 

4. Create a new database proxy and create a table prodata

 
 

  
  
create database proxy;   
  
  
CREATE TABLE prodata (name VARCHAR(20), sex CHAR(1)); 
 
 

5. create user test with full operation permissions on the proxy Database

 
 

  
  
grant all privileges on proxy.* to test@localhost identified by "zhaogongyu"; 
 
 

6. Allow the test user to log on to MySQL from any machine.

 
 

  
  
Step 1: grant all privileges on proxy. * to test @ "%" identified by "zhaogongyu ";
  
  
 
  
  
Step 2: $ sudo gedit/etc/mysql/my. cnf
 
 

Old Version

 
 

  
  
skip-networking => #skip-networking 
 
 

New Version

 
 

  
  
Bind-address = 127.0.0.1 => bind-address = IP address of your machine
 
 

This allows other machines to access MySQL.

7. Restart the mysql Command

 
 

  
  
sudo /etc/init.d/mysql restart 
 
 

Note: location after mysql Installation

Execution File

 
 

  
  
/usr/bin/mysql 
 
 

Database

 
 

  
  
/var/lib/mysql  
  
  
/var/log/mysql 
 
 

Control File

 
 

  
  
/etc/mysql/my.cnf   
 
 

Database command line operations

2. Create a database named MYSQLDATA

 
 

  
  
mysql> CREATE DATABASE MYSQLDATA; 
 
 

3. Select the database you created

 
 

  
  
Mysql> use mysqldata; (when you press the Enter key to see Database changed, the operation is successful !)
 
 

4. view the tables in the current database

 
 

  
  
mysql> SHOW TABLES; 
 
 

5. Create a database table

 
 

  
  
mysql> CREATE TABLE MYTABLE (name VARCHAR(20), sex CHAR(1)); 
 
 

6. display table structure:

 
 

  
  
mysql> DESCRIBE MYTABLE; 
 
 

7. Add records to the table

 
 

  
  
mysql> insert into MYTABLE values ("hyq","M"); 
 
 

8. load data into a database table in text format, for example, D:/mysql.txt)

 
 

  
  
mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" INTO TABLE MYTABLE; 
 
 

9. Import the. SQL FILE command such as D:/mysql. SQL)

 
 

  
  
mysql>use database;  
  
  
mysql>source d:/mysql.sql; 
 
 

10. delete a table

 
 

  
  
mysql>drop TABLE MYTABLE; 
 
 

11. Clear the table

 
 

  
  
mysql>delete from MYTABLE; 
 
 

12. Update table data

 
 

  
  
mysql>update MYTABLE set sex="f" where name='hyq';  
  
  
 
  
  
http://hi.baidu.com/selfcool/blog/item/a44e101fce79b1f2e0fe0b9d.html 
 
 

Ii. mysql-proxy

LUA file Introduction

 
 

  
  
Http://www.oreillynet.com/pub/a/databases/2007/07/12/getting-started-with-mysql-proxy.html? Page = 1
  
  
 
  
  
192.168.1.38 proxy
  
  
192.168.1.44 slaver slave
  
  
192.168.1.55 master host
  
  
 
  
  
Apt-get mysql-proxy
 
 

Configuration

1. read/write splitting script

Modify the default connection and perform a quick test. If this parameter is not modified, the read/write splitting takes effect only when the number of connections reaches.

 
 

  
  
Vim/usr/share/mysql-proxy/rw-splitting.lua
  
  
Proxy. global. config. rwsplit = {
  
  
Min_idle_connections = 1, # The default value is 4.
  
  
Max_idle_connections = 1, # The default value is 8.
  
  
 
  
  
Is_debug = false
  
  
}
 
 

2. read/write splitting

 
 

  
  
Mysql-proxy -- proxy-read-only-backend-addresses = 10.0.0.6: 3306 -- proxy-backend-addresses = 10.0.0.5: 3306
  
  
-- Proxy-lua-script =/usr/share/mysql-proxy/rw-splitting.lua>/var/log/mysql-proxy/mysql-proxy.log &
  
  
 
  
  
New/var/log/mysql-proxy/and mysql-proxy.log adjustment attribute 775 belongs to chown R mysql. adm xx Group)
  
  

  
  
Sudo mysql-proxy -- proxy-read-only-backend-addresses = 192.168.1.44: 3306
  
  
-- Proxy-backend-addresses = 192.168.1.55: 3306
  
  
-- Proxy-lua-script =/usr/share/mysql-proxy/rw-splitting.lua>/var/log/mysql-proxy/mysql-proxy.log &
  
  
)
 
 

NOTE: If no 4040 error occurs after the sudo mysql-proxy Query Process is run, restart mysql (sudo/etc/init. d/mysql restart) and enter proxy settings.

3. View Processes

 
 

  
  
netstat -ant  
  
  
tcp  00 0.0.0.0:40400.0.0.0:*LISTEN   
  
  
tcp  00 0.0.0.0:40410.0.0.0:*LISTEN  
 
 

Started successfully

You can remotely connect to a linux host and perform some read/write operations.

4. Test read/write splitting

Go to the mysql-proxy host

 
 

  
  
a@a-desktop:/usr/share/mysql-proxy$ mysql -utest -p -P4040 -h192.168.1.38  
  
  
Enter password:  
  
  
Welcome to the MySQL monitor.  Commands end with ; or \g.  
  
  
Your MySQL connection id is 25  
  
  
Server version: 5.0.51a-3ubuntu5.4-log  
  
  
 
  
  
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.  
  
  
 
  
  
mysql> use proxy;  
  
  
Database changed 
 
 

Insert a record

 
 

  
  
mysql> insert into prodata(name) values("zgy");  
  
  
Query OK, 1 row affected (0.01 sec)  
  
  
 
  
  
mysql>select * from proxy;  
  
  
Empty set (0.00 sec) 
 
 

No result is returned when data is inserted ~ The read/write splitting is correct ~~~~

Note: Sometimes data is displayed in the mysql_proxy (38) database. restart the system and restart mysql.

Go to the master write server (192.168.1.55) to view the data

 
 

  
  
mysql -utest -p -h192.168.1.55  
  
  
use proxy;  
  
  
select * from prodata; 
 
 

Check that a record has been written.

Access the slave read server (192.168.1.44)

 
 

  
  
mysql -utest -p -h192.168.1.44  
  
  
use proxy;  
  
  
select * from prodata; 
 
 

Because no data is displayed

It indicates that only the read and write operations are allowed.

Summary:Ubuntu 9.04InstallMysql Proxy LuaThe content of the successful test version has been introduced. I hope this article will help you!