Stivlo'st in Asia

On Ubuntu I installed proftpd and proftd-mysql packages.

I add the following tables and the proftpd user to mysql.

CREATE DATABASE `proftpd` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

--
-- Table structure for table 'ftpgroup'
--

CREATE TABLE ftpgroup (
groupname varchar(16) NOT NULL default '',
gid smallint(6) NOT NULL default '5500',
members varchar(16) NOT NULL default '',
KEY groupname (groupname)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='ProFTP group table';

-- --------------------------------------------------------

--
-- Table structure for table 'ftpuser'
--

CREATE TABLE ftpuser (
id int(10) unsigned NOT NULL auto_increment,
userid varchar(30) NOT NULL default '',
passwd varchar(30) NOT NULL default '',
uid smallint(6) NOT NULL default '33',
gid smallint(6) NOT NULL default '5500',
homedir varchar(255) NOT NULL default '',
shell varchar(16) NOT NULL default '/sbin/nologin',
count int(11) NOT NULL default '0',
accessed datetime NOT NULL default '0000-00-00 00:00:00',
modified datetime NOT NULL default '0000-00-00 00:00:00',
domain varchar(32) NOT NULL default '',
PRIMARY KEY  (id),
UNIQUE KEY userid (userid),
KEY domain (domain)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='ProFTP user table';

INSERT INTO ftpgroup (groupname, gid, members) VALUES
('ftpgroup', 5500, 'ftpuser');

INSERT INTO ftpuser (id, userid, passwd, uid, gid, homedir, shell, count, accessed, modified, domain)
VALUES (1, 'web', 'xxx', 5000, 5500, '/var/www', '/sbin/nologin', 1,
'', '', 'test.com');

INSERT INTO mysql.user (Host, User, Password, Select_priv, Insert_priv,
Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv,
Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv,
Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv,
Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv,
Create_view_priv, Show_view_priv, Create_routine_priv,
Alter_routine_priv, Create_user_priv, ssl_type, max_questions,
max_updates, max_connections, max_user_connections
) VALUES (
'localhost', 'proftpd', PASSWORD( 'xxxx' ) , 'N', 'N', 'N', 'N', 'N',
'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N',
'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '0', '0', '0', '0'
);

INSERT INTO mysql.db (Host, Db, User, Select_priv, Insert_priv,
Update_priv, Delete_priv, Create_priv, Drop_priv, Grant_priv,
References_priv, Index_priv, Alter_priv, Create_tmp_table_priv,
Lock_tables_priv, Create_view_priv, Show_view_priv, Create_routine_priv,
Alter_routine_priv, Execute_priv
) VALUES (
'localhost', 'proftpd', 'proftpd', 'Y', 'N', 'Y', 'N', 'N', 'N', 'N',
'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N'
);

FLUSH PRIVILEGES;

uncomment and customize /etc/proftpd/sql.conf

SQLUserInfo         ftpuser userid passwd uid gid homedir shell
SQLGroupInfo       ftpgroup groupname gid members

uncomment

Include /etc/proftpd/sql.conf in /etc/proftpd/proftpd.conf

A view from the bus, around Ekkamai.

Today I’m installing a Webserver that has access to the internet restricted for security reasons. Since the base operative system is Ubuntu Linux, I’ve to tell apt-get to use a proxy.

To do this, I just have to add the following lines to /etc/bash.bashrc file :

export http_proxy=http://username:password@proxyserver.net:port/
export ftp_proxy=http://username:password@proxyserver.netport/

Obviuosly customized with my username, password and proxy server.

It’s interesting that MySQL replication works also with an unreliable connection. It won’t be up to date all the time, obviously, but it should still work. With this in mind I wanted to keep a copy of a database on my laptop on another computer of the office. I want to keep my laptop on 24 hours a day because it use less power than the desktop computer.

My database starts empty with just 3 tables, but it will then be filled with millions of rows later. So the first step is simply dump the schema to the other computer, I don’t have to worry about changes because it’s going to stay empty until I start the feeds.

1) Set up my master laptop to write the binary log and assign an unique id

[mysqld]
log-bin=mysql-bin
server-id=31

2) Restart MySQL. in my C:\Program Files\MySQL\MySQL5\data directory two new files appear: mysql-bin.000001 and mysql-bin.index. It means the binary log is working.

3) Create the user for replication:

GRANT REPLICATION SLAVE ON *.*
TO ‘replication’@’192.168.1.37′ IDENTIFIED BY ‘mypassword’;

4) My slave is a Linux Ubuntu. It has already server-id set to 30

5) On the master I try to find the current position in the binary log

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER STATUS G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 250
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

6) I’ve already done my data snapshot, nothing to worry here, only 3 empty tables.

7) I only need to replicate one db, so I filter it on the slave with replicate-do-db=mydb (in my.cnf)

After restarting MySQL on the slave, I execute the change master statement.

CHANGE MASTER TO
MASTER_HOST=’192.168.1.40′,
MASTER_PORT=33060,
MASTER_USER=’replication’,
MASTER_PASSWORD=’mypassword’,
MASTER_LOG_FILE=’mysql-bin.000001′,
MASTER_LOG_POS=250,
MASTER_CONNECT_RETRY = 3600;
Query OK, 0 rows affected (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.40
Master_User: replication
Master_Port: 33060
Connect_Retry: 3600
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 43674
Relay_Log_File: mysqld-relay-bin.000006
Relay_Log_Pos: 43811
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: mydb
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 43674
Relay_Log_Space: 43811
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
Now the easiest way to test, is insert some rows in the master db and see if they appear in the slave db.. and it really happened so, it’s working!