Difference between revisions of "PowerDNS: Create PowerDNS Database dan User di Percona / MySQL"
Jump to navigation
Jump to search
Onnowpurbo (talk | contribs) |
Onnowpurbo (talk | contribs) |
||
Line 1: | Line 1: | ||
− | Masuk ke | + | Masuk ke database |
− | sudo mysql -u root -p123456 | + | sudo mysql -u root -h ::1 -p123456 |
− | + | Siapkan tabel | |
− | + | CREATE DATABASE powerdns; | |
− | |||
− | |||
− | + | GRANT ALL ON powerdns.* TO 'powerdns'@'localhost' \ | |
− | + | IDENTIFIED BY 'ubuntu'; | |
+ | |||
+ | FLUSH PRIVILEGES; | ||
− | + | USE powerdns; | |
− | |||
− | |||
− | + | CREATE TABLE domains ( | |
+ | id INT AUTO_INCREMENT, | ||
+ | name VARCHAR(255) NOT NULL, | ||
+ | master VARCHAR(128) DEFAULT NULL, | ||
+ | last_check INT DEFAULT NULL, | ||
+ | type VARCHAR(6) NOT NULL, | ||
+ | notified_serial INT UNSIGNED DEFAULT NULL, | ||
+ | account VARCHAR(40) CHARACTER SET 'utf8' DEFAULT NULL, | ||
+ | PRIMARY KEY (id) | ||
+ | ) Engine=InnoDB CHARACTER SET 'latin1'; | ||
− | + | CREATE UNIQUE INDEX name_index ON domains(name); | |
− | + | ||
− | + | CREATE TABLE records ( | |
− | + | id BIGINT AUTO_INCREMENT, | |
− | CREATE | + | domain_id INT DEFAULT NULL, |
− | + | name VARCHAR(255) DEFAULT NULL, | |
− | + | type VARCHAR(10) DEFAULT NULL, | |
− | + | content VARCHAR(64000) DEFAULT NULL, | |
− | CREATE TABLE | + | ttl INT DEFAULT NULL, |
− | + | prio INT DEFAULT NULL, | |
− | + | change_date INT DEFAULT NULL, | |
− | + | disabled TINYINT(1) DEFAULT 0, | |
− | + | ordername VARCHAR(255) BINARY DEFAULT NULL, | |
− | + | auth TINYINT(1) DEFAULT 1, | |
− | + | PRIMARY KEY (id) | |
− | + | ) Engine=InnoDB CHARACTER SET 'latin1'; | |
− | + | ||
− | ); | + | CREATE INDEX nametype_index ON records(name,type); |
− | CREATE | + | CREATE INDEX domain_id ON records(domain_id); |
+ | CREATE INDEX ordername ON records (ordername); | ||
+ | |||
+ | CREATE TABLE supermasters ( | ||
+ | ip VARCHAR(64) NOT NULL, | ||
+ | nameserver VARCHAR(255) NOT NULL, | ||
+ | account VARCHAR(40) CHARACTER SET 'utf8' NOT NULL, | ||
+ | PRIMARY KEY (ip, nameserver) | ||
+ | ) Engine=InnoDB CHARACTER SET 'latin1'; | ||
+ | |||
+ | CREATE TABLE comments ( | ||
+ | id INT AUTO_INCREMENT, | ||
+ | domain_id INT NOT NULL, | ||
+ | name VARCHAR(255) NOT NULL, | ||
+ | type VARCHAR(10) NOT NULL, | ||
+ | modified_at INT NOT NULL, | ||
+ | account VARCHAR(40) CHARACTER SET 'utf8' DEFAULT NULL, | ||
+ | comment TEXT CHARACTER SET 'utf8' NOT NULL, | ||
+ | PRIMARY KEY (id) | ||
+ | ) Engine=InnoDB CHARACTER SET 'latin1'; | ||
+ | |||
+ | CREATE INDEX comments_name_type_idx ON comments (name, type); | ||
+ | CREATE INDEX comments_order_idx ON comments (domain_id, modified_at); | ||
+ | |||
+ | CREATE TABLE domainmetadata ( | ||
+ | id INT AUTO_INCREMENT, | ||
+ | domain_id INT NOT NULL, | ||
+ | kind VARCHAR(32), | ||
+ | content TEXT, | ||
+ | PRIMARY KEY (id) | ||
+ | ) Engine=InnoDB CHARACTER SET 'latin1'; | ||
− | + | CREATE INDEX domainmetadata_idx ON domainmetadata (domain_id, kind); | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | CREATE INDEX | ||
− | CREATE TABLE | + | CREATE TABLE cryptokeys ( |
− | + | id INT AUTO_INCREMENT, | |
− | + | domain_id INT NOT NULL, | |
− | + | flags INT NOT NULL, | |
− | PRIMARY KEY ( | + | active BOOL, |
− | ); | + | content TEXT, |
+ | PRIMARY KEY(id) | ||
+ | ) Engine=InnoDB CHARACTER SET 'latin1'; | ||
− | + | CREATE INDEX domainidindex ON cryptokeys(domain_id); | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | CREATE INDEX | ||
− | CREATE TABLE | + | CREATE TABLE tsigkeys ( |
− | id INT AUTO_INCREMENT, | + | id INT AUTO_INCREMENT, |
− | + | name VARCHAR(255), | |
− | + | algorithm VARCHAR(50), | |
− | + | secret VARCHAR(255), | |
− | PRIMARY KEY (id) | + | PRIMARY KEY (id) |
− | ) | + | ) Engine=InnoDB CHARACTER SET 'latin1'; |
− | |||
− | CREATE | + | CREATE UNIQUE INDEX namealgoindex ON tsigkeys(name, algorithm); |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | + | QUIT | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− |
Latest revision as of 09:23, 20 February 2019
Masuk ke database
sudo mysql -u root -h ::1 -p123456
Siapkan tabel
CREATE DATABASE powerdns; GRANT ALL ON powerdns.* TO 'powerdns'@'localhost' \ IDENTIFIED BY 'ubuntu'; FLUSH PRIVILEGES; USE powerdns; CREATE TABLE domains ( id INT AUTO_INCREMENT, name VARCHAR(255) NOT NULL, master VARCHAR(128) DEFAULT NULL, last_check INT DEFAULT NULL, type VARCHAR(6) NOT NULL, notified_serial INT UNSIGNED DEFAULT NULL, account VARCHAR(40) CHARACTER SET 'utf8' DEFAULT NULL, PRIMARY KEY (id) ) Engine=InnoDB CHARACTER SET 'latin1'; CREATE UNIQUE INDEX name_index ON domains(name); CREATE TABLE records ( id BIGINT AUTO_INCREMENT, domain_id INT DEFAULT NULL, name VARCHAR(255) DEFAULT NULL, type VARCHAR(10) DEFAULT NULL, content VARCHAR(64000) DEFAULT NULL, ttl INT DEFAULT NULL, prio INT DEFAULT NULL, change_date INT DEFAULT NULL, disabled TINYINT(1) DEFAULT 0, ordername VARCHAR(255) BINARY DEFAULT NULL, auth TINYINT(1) DEFAULT 1, PRIMARY KEY (id) ) Engine=InnoDB CHARACTER SET 'latin1'; CREATE INDEX nametype_index ON records(name,type); CREATE INDEX domain_id ON records(domain_id); CREATE INDEX ordername ON records (ordername); CREATE TABLE supermasters ( ip VARCHAR(64) NOT NULL, nameserver VARCHAR(255) NOT NULL, account VARCHAR(40) CHARACTER SET 'utf8' NOT NULL, PRIMARY KEY (ip, nameserver) ) Engine=InnoDB CHARACTER SET 'latin1'; CREATE TABLE comments ( id INT AUTO_INCREMENT, domain_id INT NOT NULL, name VARCHAR(255) NOT NULL, type VARCHAR(10) NOT NULL, modified_at INT NOT NULL, account VARCHAR(40) CHARACTER SET 'utf8' DEFAULT NULL, comment TEXT CHARACTER SET 'utf8' NOT NULL, PRIMARY KEY (id) ) Engine=InnoDB CHARACTER SET 'latin1'; CREATE INDEX comments_name_type_idx ON comments (name, type); CREATE INDEX comments_order_idx ON comments (domain_id, modified_at); CREATE TABLE domainmetadata ( id INT AUTO_INCREMENT, domain_id INT NOT NULL, kind VARCHAR(32), content TEXT, PRIMARY KEY (id) ) Engine=InnoDB CHARACTER SET 'latin1'; CREATE INDEX domainmetadata_idx ON domainmetadata (domain_id, kind); CREATE TABLE cryptokeys ( id INT AUTO_INCREMENT, domain_id INT NOT NULL, flags INT NOT NULL, active BOOL, content TEXT, PRIMARY KEY(id) ) Engine=InnoDB CHARACTER SET 'latin1'; CREATE INDEX domainidindex ON cryptokeys(domain_id); CREATE TABLE tsigkeys ( id INT AUTO_INCREMENT, name VARCHAR(255), algorithm VARCHAR(50), secret VARCHAR(255), PRIMARY KEY (id) ) Engine=InnoDB CHARACTER SET 'latin1'; CREATE UNIQUE INDEX namealgoindex ON tsigkeys(name, algorithm); QUIT