How to set up mysql master/slave architecture.

来源:【原创】How to set up mysql master/slave architecture. (2008-12-10 09:55)

All the info below should be added in /etc/my.cnf. If you can’t find this file, just use the following statements to find where it is.

  1. To find the location of my.cnf.

ps aux | grep mysql | grep -v ‘grep’ | head -n 1

For example .

[root@localhost ~]# ps aux | grep mysql | grep -v ‘grep’ | head -n 1

root      2952  0.0  0.2   4512  1220 ?        S    16:12   0:00 /bin/sh ./mysqld_safe –defaults-file=/usr/local/mysql/my.cnf

Then we know the correct configuration file is /usr/local/mysql/my.cnf.

We should add the following lines to my.cnf in section [mysqld].

server-id = 1

log-bin=mysql-bin

binlog-do-db=db1

binlog-do-db=db2

binlog-ignore-db=ignore-db1

binlog-ignore-db=ignore-db2

expire_logs_days = 5

max_binlog_size=500M

log-slave-updates

Then restart mysqld manually and execute the following statements in mysql command line client.

  1. To grant valid user to slave.

To assume this thread called A.

grant file,replication slave on *.* to ‘mysql_ms’@’Slave IP’ identified by ‘Your password’;

flush privileges;

For example, if my slave’s ip address is "192.168.4.55". The user is "mysql_ms" and his password is "123456".

mysql> grant replication slave,file on *.* to ‘mysql_ms’@’192.168.4.55’ identified by ‘123456’;

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

Be sure to send the username and password to me.

  1. Lock tables and get the master’s binary log file and position.

flush tables with read lock;

show master status\G

You must not quit the current mysql command line client.

mysql> show master status\G

*************************** 1. row ***************************

            File: mysql-bin.000004

        Position: 595

Binlog_Do_DB: test

Binlog_Ignore_DB: mysql

1 row in set (0.00 sec)

mysql>

Then send the results to me.

  1. Backup exact database.

Now we locked all the tables and got the exact binary log file and position in the previous step. So begin to backup the exact database right now.

Use mysqldump to dump the necessary data to flat file, then use tool named gzip or gzip2 to compress it.

For example, if your database name is db1 and your mysql installation path was added in the environment variable named "PATH".

Use the following statement to backup your databases’ data.

mysqldump -uroot -p –net_buffer_length=10M –max_allowed_packet=11M db1 > db1.txt

Here is my example in my machine, my database name is test.

[root@localhost ~]# /usr/local/mysql/bin/mysqldump -uroot -p –net_buffer_length=100M –max_allowed_packet=120M test > test.txt

To compress the flat text file to a gzip file, using the following command.

gzip db1.txt

Then the compressed file named db1.txt.gz will be generated. You should send this file to me.

After all the above completes, go to thread A and execute the following command.

unlock tables.

Then quit thread A.

  1. What the slave machine want.

User name and password.

Master’s IP address.(192.168.4.54)

Master’s  mysql port.(3308)

Master’s  mysql binary log file and position

Master’s backup data.

  1. The following is the slave configuration.

Add the my.cnf on slave machine and restart mysqld.

[mysqld]

server-id = 2

replicate-do-db=db1

replicate-do-db=db2

replicate-ignore-db=ignore-db1

replicate-ignore-db=ignore-db2

log-bin=slave-bin

  1. Get the master information on slave machine.

Then exit from it and use the following command to import from the backup file in the shell environment.

gzip gb1.txt.gz

mysql –uroot –p –S/tmp/mysql3307.sock < gb1.txt

Enter the mysql command line client.

set @@global.max_allowed_packet=11*1024*1024;

Then exit it and enter it again.

Change master to

master_host=’192.168.4.54’,

master_port=3308,

master_user=’mysql_ms’,

master_password=’123456’

master_log_file=’mysql-bin.000004’,

master_log_pos=595;

start slave;

innodb引擎Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.

今天在做innodb参数配置的时候,对其默认配置加以修改–结果导致启动后的server竟然不支持innodb引擎。Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
错误日志:
InnoDB: Data file /usr/local/mysql4/data/ibdata2 did not exist: new to be created
InnoDB: Setting file /usr/local/mysql4/data/ibdata2 size to 20 MB
InnoDB: Database physically writes the file full: wait…
InnoDB: No valid checkpoint found.
InnoDB: If this error appears when you are creating an InnoDB database,
InnoDB: the problem may be that during an earlier attempt you managed
InnoDB: to create the InnoDB data files, but log file creation failed.
InnoDB: If that is the case, please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/error-creating-innodb.html
[ERROR] Plugin ‘InnoDB’ init function returned error.
[ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
在遇到这些情况的时候,我起初考虑是没有建立新日志文件的权限。
实则不然
当我在未正常关闭server的情况下对其参数修改,
修改之后没有删除ib_logfile文件,ib_logfile文件中记录些innodb引擎非常有用的信息比如说默认的innodb默认的配置信息,你又是在未正常关闭server情况下操作的,所以导致重启后的server不支持innodb引擎。
rm -rf ib_logfile*
正确配置参数
重启server。

windows下Mysql解决启动1067和Fatal error: Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist

Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist

2011年02月26日 星期六 01:43

关于手动修改mysql目录下的my.ini的数据库保存路径后,在启动的时候出错。

查看错误日志后,发现Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist

原错误日志内容:

110226 1:20:46 [Note] Plugin ‘FEDERATED’ is disabled.

C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: Table ‘mysql.plugin’ doesn’t exist

110226 1:20:46 [ERROR] Can’t open the mysql.plugin table. Please run mysql_upgrade to create it.

InnoDB: The first specified data file D:\mysite\database\ibdata1 did not exist:

InnoDB: a new database to be created!

110226 1:20:47 InnoDB: Setting file D:\mysite\database\ibdata1 size to 10 MB

InnoDB: Database physically writes the file full: wait…

110226 1:20:47 InnoDB: Log file .\ib_logfile0 did not exist: new to be created

InnoDB: Setting log file .\ib_logfile0 size to 10 MB

InnoDB: Database physically writes the file full: wait…

110226 1:20:47 InnoDB: Log file .\ib_logfile1 did not exist: new to be created

InnoDB: Setting log file .\ib_logfile1 size to 10 MB

InnoDB: Database physically writes the file full: wait…

InnoDB: Doublewrite buffer not found: creating new

InnoDB: Doublewrite buffer created

InnoDB: Creating foreign key constraint system tables

InnoDB: Foreign key constraint system tables created

110226 1:20:48 InnoDB: Started; log sequence number 0 0

110226 1:20:48 [ERROR] Fatal error: Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist

从网上查了半天都是linux下的解决方法,没有windows的。不过仔细看了看linux下的解决方法,发现是因为

windows下Mysql解决启动1067和Fatal error: Can't open and lock

这个文件闹得,从日志上来看是因为不能够打开私有权限的表格。

这个文件闹得,从日志上来看是因为不能够打开私有权限的表格。

解决方法直接把这个文件夹全都复制到你要修改的目录下,重启mysql就ok了。

纪录片的费用为什么贵

阿弋:纪录片的费用为什么贵

社会往往混淆了纪录片与电视专题片的区别,电视专题片是报道类纪实型的电视专题片,是电视深度报道最常用的节目形态。电视新闻专题片的基本特征是新闻性较强,反映当前重大新闻事件或社会普遍关注的热点和难点问题;有明显纪实风格,不允外加虚假操作。但同时讲究艺术性。电视新闻专题片与电视新闻一样,强调报道词与画面的有机组合,是所谓“双主体”的并重关系。如《走遍中国》、《探索发现》。纪录片是以真实生活为创作素材,以真人真事为表现对象,并对其进行艺术的加工与展现的,以展现真实为本质,并用真实引发人们思考的电影或电视艺术形式。纪录片的核心为真实。电影的诞生始于纪录片的创作。1895年法国路易·卢米埃尔拍摄的《工厂的大门》、《火车进站》等实验性的电影,都属于纪录片的性质。中国纪录电影的拍摄始于19世纪末和20世纪初,第一部是1905年的《定军山》。最早的一些镜头,包括清朝末年的社会风貌,八国联军入侵中国的片断和历史人物李鸿章等,是由外国摄影师拍摄的。纪录片又可以分为电影纪录片和电视纪录片。1895年的《工厂大门》、《火车进站》等早期电影作品,是纪录片的萌芽形态。纪录片分类:宣传纪录片、商业纪录片、独立纪录片(当然,根据分类标准不同,还有其他分类方法)。

纪录片制作一集制作费用比电视剧还贵。外国做纪录片一集高达200万美金,中国一集纪录片费用在100万-150万元人民币。一般当代题材电视剧费用50万左右,古装戏80万一集-150万左右,由此看来,专题片比电视剧贵很多。

纪录片是电影的祖宗,最初的电影都是纪录片,缺少艺术创作因素,都是生活的实录和真相。